是否有必要在Linq中使用连接到Sql

I was practicing today when I realized that there are two ways linq to sql can retrieve data from db, I created two datagrid and used the two different ways to populate each of these datagrids and they produced the same result.

我今天练习的时候,我意识到linq to sql可以通过两种方式从db中检索数据,我创建了两个datagrid并使用两种不同的方式来填充每个datagrids,它们产生了相同的结果。

The first method is using joins to get data from related tables, and the other methods is using linq query like an object to access related tables. The code is shown below:

第一种方法是使用连接从相关表中获取数据,其他方法使用linq查询作为对象访问相关表。代码如下所示:

NorthWindDataContext dbContext = new NorthWindDataContext();

        var orders = from ord in dbContext.Orders
                     select new { ord.ShipCountry , ord.Customer.ContactName};

        var orders2 = from ord in dbContext.Orders
                     join cust in dbContext.Customers on ord.CustomerID equals cust.CustomerID
                     select new
                     {
                         ord.ShipCountry, cust.ContactName
                     };
        var data = orders2;

        DataGrid.ItemsSource= orders;
        DataGrid2.ItemsSource = orders2;

My question like the title is if it is entirely necessary to use joins, because I find them really cumbersome to use sometimes.

像标题这样的问题是,如果完全有必要使用连接,因为我发现它们有时候使用起来非常麻烦。

2 个解决方案

#1

You need to use something that gets you from the order to the customer.

您需要使用从订单到客户的东西。

Join can do this. This is how the second query works.

加入可以做到这一点。这是第二个查询的工作方式。

Having the order "know" about the customer can do this. This is how the first query works.

让订单“了解”客户可以做到这一点。这是第一个查询的工作方式。

If your data provider is aware of the connection between order and customer then these will amount to the same thing.

如果您的数据提供商知道订单与客户之间的联系,那么这些将相同。

If your data provider is not aware of the connection, then the approach in the first example would result in N + 1 look ups instead of 1.

如果您的数据提供者不知道连接,那么第一个示例中的方法将导致N + 1查找而不是1。

A linq-friendly ORM will generally be aware of these connections as long as the appropriate relationship-marking attributes are present (just what that is differs between Linq2SQL, EF, NHibernate, etc.).

只要存在适当的关系标记属性(Linq2SQL,EF,NHibernate等之间的差别),linq友好的ORM通常会知道这些连接。

It's still important to know the join approach for cases where either the relationship isn't known about by the provider, or you have a reason to join on something other than a foreign-key relationship.

对于提供者不了解关系的情况,或者您有理由加入除外键关系之外的其他事情,了解连接方法仍然很重要。

#2

The answer is "sort of". Since you're using an ORM such as Linq-to-Sql, no you don't directly need to call join within your linq queries to accomplish what you're trying to do.

答案是“有点”。由于您正在使用诸如Linq-to-Sql之类的ORM,因此您不需要直接在linq查询中调用join来完成您正在尝试执行的操作。

However, when the ORM activates the query it will generate actual SQL code that'll have a join statement in it to get the results you're querying. Since you're using an ORM though, the data returned is mapped to objects, and since Customer has a relationship between the objects, the relationship will also be translated to from the database INTO the objects.

但是,当ORM激活查询时,它将生成实际的SQL代码,其中包含一个join语句以获取您正在查询的结果。由于您正在使用ORM,因此返回的数据将映射到对象,并且由于Customer在对象之间存在关系,因此关系也将从数据库INTO转换为对象。

ord.Customer.ContactName

The above statement is most likely translated to a JOIN statement performing an INNER JOIN between Customer & Orders.

上述语句很可能转换为在客户和订单之间执行INNER JOIN的JOIN语句。

Due to this, both of your LINQ queries most likely generating similar SQL queries. Both of which has a JOIN statement in them. Because the relationships between your objects also exists within the database (and everything is mapped together showing this relationship) you don't directly need to use join within a LINQ statement.

因此,两个LINQ查询很可能生成类似的SQL查询。两者都有一个JOIN语句。由于对象之间的关系也存在于数据库中(并且所有内容都映射在一起显示此关系),因此您不需要在LINQ语句中使用连接。

#1