如何在sql中创建一个临时表来进行linq查询 [英] how to create a temporary table in sql to linq query

问题描述

你好朋友，

你能帮我在linq查询中创建一个临时表。我已经推荐了很多站点但没有得到合适的解决方案，最后我在这里发布了我的问题



我的sql查询是：

  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.TblOrganizationU nits.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 
其中OrganizationUnitId不在（SELECT ParentUnitId FROM Node）
 option（maxrecursion 0）; ，OrganizationUnitId）; 

这里节点是临时表，Organization.TblOrganizationUnits是我的表格在sql中，很容易创建一个临时表但是在linq中如何创建一个temorary表以及我如何执行上述查询的不同连接和联合操作。

请帮助我修复它。

感谢提前帮助我。

解决方案

在LINQ中创建临时表是不可能的。它用于处理现有的表。

因此，一种方法是使用存储过程处理LINQ并操作存储过程中的所有内容。



参考 - LINQ to SQL（第6部分） - 使用存储过程检索数据） [ ^ ]。

Amit，您应该知道Node实际上不是临时表（虽然，我同意，实际上它的作用可能就像一个）。节点是公用表表达式。没有为它保留存储空间，它只能在声明它的语句中访问。



现在，这并没有开始解决你的问题 - 但是（特别是在我们的职业中）我们用于事物的名称准确无误。并且 - 当你在寻找问题的答案时它会有所帮助：一个快速的谷歌在linq中使用CTE到sql 产生（以及其他）以下内容：http://stackoverflow.com/questions/584841/common-table-expression-cte- in-linq-to-sql [ ^ ]依次引用如何：直接执行SQL查询[在Linq-to-Sql] [ ^ ]。

hello friends,
can you help me to create a temporary table in linq query.i have referred lots of sites but not getting the appropriate solution,finally i post my problem here

my sql query is:

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)
                               option (maxrecursion 0); ", OrganizationUnitId);

here "Node" is temporary table,and "Organization.TblOrganizationUnits" is table in my database.In sql it's easy to create a temporary table but in linq how can i create a temorary table and how i perform different joins and union operation of the above query.
kindly help me to fix it.
Thanks in Advance for helping me.

解决方案

Creating Temporary Table in LINQ is not possible. It is used to deal with existing Tables.
So, one way will be to deal LINQ with Stored Procedure and operate everything inside the Stored Procedure.

Refer- LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)[^].

Amit, you should know that Node is not, in fact, a temporary table (although, I agree, in effect is works like it might as well be one). Node is a Common Table Expression. No storage is reserved for it, and it is only accessible to the statement in which it is declared.

Now, that doesn't begin to address your question - but (especially in our profession) it never hurts to be accurate in the names we use for things. And - it helps when you're searching for answers to questions: A quick Google for use CTE in linq to sql yielded (amongst others) the following: http://stackoverflow.com/questions/584841/common-table-expression-cte-in-linq-to-sql[^] which, in turn references How To: Directly Execute SQL Queries [in Linq-to-Sql][^].

这篇关于如何在sql中创建一个临时表来进行linq查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！