how to create a temporary table in sql to linq query

Question

_{Monday, December 23, 2013 2:05 PM}

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;

        }

All replies (2)

_{Monday, December 23, 2013 3:10 PM ✅Answered}

LINQ is used for querying data.  Creating temp tables isn't something you'll really be doing in LINQ (although you can create intermediaries using into).  Normally in SQL if you need a temp table it is because you're building up data that cannot be represented easily in a SQL join.  For LINQ all you'd do is query the data and store the results into a .NET variable.  You can then use the .NET variable in later LINQ queries.  But a temp table is not something that you should be striving for.

Your query appears to be building up a hierarchy of organizations.  It is pretty common in SQL to use a CTE for this.  LINQ doesn't have an equivalent because it doesn't really need it.  The simplest solution would be to leave the query as it is and not use LINQ at all.  There is nothing wrong with this approach.  Not everything needs to be LINQ.  The next cleanest solution would be to use EF or another ORM where you can represent the query using .NET objects.

//EF approach
[Table("TblOrganizationUnits")]
class OrganizationUnit
{
    [Key]
    public int OrganizationUnitId { get; set; }

    public string UnitName { get; set; }

    public int? ParentUnitId { get; set; }

    [ForeignKey("ParentUnitId")]
    public virtual OrganizationUnit ParentUnit { get; set; }
}

class TestContext : DbContext
{
    public IDbSet<OrganizationUnit> OrganizationUnits { get; set; }
}

//Getting root units with all their children
var rootUnits = from u in ctx.OrganizationUnits
                where u.ParentUnit == null
                select u;

I noticed that your query is going through a lot of work to build up the hierarchy but ultimately it just returns back the # of rows returned.  Based upon my understanding of your code that would seem to just be the # of organizations so you don't need the CTE at all. But perhaps this is part of a larger solution where you do need the hierarchy.

Michael Taylor
http://msmvps.com/blogs/p3net

_{Monday, December 23, 2013 3:31 PM ✅Answered}

Hi,

Node isn't a temporary table but a common table expression. CTE expressions are compiled into the query plan, and the result set is determined by the SELECT clause. If you are only need the number of "nodes" use

WITH Node (...)
SELECT  COUNT(*) AS NodeCount FROM Node
where  OrganizationUnitId not in (SELECT  ParentUnitId FROM   Node)

As LINQ To SQL doesn't support CTEs you will have to use a query, see How to: Directly Execute SQL Queries

and also applies to the Entity Framework: http://stackoverflow.com/questions/13535003/common-table-expression-in-entityframework

Regards, Elmar