如何使用常规SQL和Linq-To-Entities通过1-2个查询检索所有记录? [英] How I can retrieve all records with 1-2 queries using regular SQL and Linq-To-Entities?

问题描述

我在Picasa上上传了简化的 SQL ERD .我尝试执行此操作，但是通过两个以上的查询进行了处理.

我需要从表 D 中检索具有表 E 的特定 E_Id 和表 TypeId 的所有记录> A
我还需要从B匹配TypeId的所有记录都将被检索，即使不是所有的记录都在表C中匹配(某种LEFT-RIGHT JOIN)

我最多需要执行2个查询，然后将其转换为Linq-To-Entites语句.如果可以通过1个查询完成，那么我更喜欢使用1个查询

谢谢

I uploaded simplified SQL ERD on Picasa. I tried to do it but I manage to it with more than 2 queries.

I need to retrieve all records from table D with specific E_Id of table E and specific TypeId of table A
I need also that all records from B that matches to TypeId will be alse retrieved even if not all of them are matched in table C (some kind of LEFT-RIGHT JOIN)

I need to do it at most 2 queries and then convert it to Linq-To-Entites statement If it can be done with 1 query I prefer with 1 query

Thank you

推荐答案

我假设在第一部分中，两种情况下所需的字段都是相同的.如果不是全部，您需要做的就是在表之间进行n个内部联接

I''m assuming that the fields you require are the same from both scenarios for the first part. If not all you need to do is a n inner join between the tables

select col1,col2,... from tableD where E_id =your_value and TypeID=your_type

union all

Select col1,col2,...from tableB left outer join tableC where TypeID=your_type

类似于上面的内容应该在一个查询中为您提供所有数据.如果不确定联接的语法，请在设计视图中创建一个视图，然后为每个零件一张一张地添加表并测试数据.如果没有设置外键，则只需在设计视图中拖放需要从一个表链接到另一个表的字段即可.然后，要包含左或右外部联接，只需在代码窗格中修改语法即可.

如果您需要更多详细信息，请提供有关您的要求和表结构的更多信息.

Something similar to the above should give you all the data in one query. If you are unsure about the syntax of the join create a view in design view and add the tables in one by one for each part and test the data. If you do not have foreign keys set up all you need to do is to drag and drop the field on which you need to link from one table to the other in design view. Then to include left or right outer joins just amend the syntax in the code pane.

If you need any more details please give more information about your requirements and table structure.

这篇关于如何使用常规SQL和Linq-To-Entities通过1-2个查询检索所有记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！