how to create a temporary table in sql to linq query

Question

I want to create a temporary table in linq query. I have searched for the solution but didn't succeeded. here "Node" is temporary table,and "Organization.TblOrganizationUnits" is table in my database. In linq, How can i create a temporary table and how can I perform different joins and union operation of the above query. my sql query is:

 

 

private int GetLeafNodeCount(int OrganizationUnitId)

{

// This function return the Leaf node count.

string query = string.Format(@"WITH Node (OrganizationUnitId, UnitName,ParentUnitId)

AS (

SELECT Organization.TblOrganizationUnits.OrganizationUnitId, Organization.TblOrganizationUnits.UnitName , Organization.TblOrganizationUnits.ParentUnitId

FROM Organization.TblOrganizationUnits

WHERE OrganizationUnitId ={0}

UNION ALL

SELECT Organization.TblOrganizationUnits.OrganizationUnitId, Organization.TblOrganizationUnits.UnitName, Organization.TblOrganizationUnits.ParentUnitId

FROM Organization.TblOrganizationUnits

INNER JOIN Node

ON Organization.TblOrganizationUnits.ParentUnitId = Node.OrganizationUnitId

)

SELECT OrganizationUnitId, UnitName,ParentUnitId FROM Node

where OrganizationUnitId not in (SELECT ParentUnitId FROM Node)

", OrganizationUnitId);

SqlConnection con = new SqlConnection();

con.ConnectionString = ConnectionString;

con.Open();

SqlCommand cmd = new SqlCommand(query, con);

//cmd.CommandTimeout = 0;

//create the DataAdapter & DataSet

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

con.Close();

return ds.Tables[0].Rows.Count;

}