将LEFT OUTER JOIN转换为Entity Framework [英] Converting a LEFT OUTER JOIN to Entity Framework

问题描述

这里是一个SQL查询我想要转换为EF4.3

  command = database.GetSqlStringCommand（@
 select 
 H.AUTHENTICATION_ID，
 USERNAME，
 PERMISSIONS，
 ORGANIZATION_IDENTIFIER，
 O.ORGANIZATION_ID 
从
 AUTHENTICATION H 
 left join [AUTHORIZATION] T on H.AUTHENTICATION_ID = T.AUTHENTICATION_ID 
 join ORGANIZATION O on O.ORGANIZATION_ID = T.ORGANIZATION_ID 
 order by H.AUTHENTICATION_ID）; 
  

这是最好的LINQ我可以想出：

  var query = from h in context.Authentications 
在上下文中加入t。h.AuthenticationId上的授权等于t.Authentications.AuthenticationId 
 join o in上下文。t.Organizations.OrganizationId上的组织等于o.OrganizationId 
 orderby 
 h.AuthenticationId 
 select new 
 {AUTHENTICATION_ID =（Int16？）h.AuthenticationId，
 h.Username，
 t.Permissions，
 o.OrganizationIdentifier，
 OrganizationID =（Int16？）o.OrganizationId 
}; 
  

我知道我需要合并我的第一个连接（授权和认证之间）并应用DefaultIfEmpty但不能显示语法。

EDIT：图片说明：

任何帮助将非常感激。

解决方案

Linq中的左连接的基本语法如下：

从表1中的x
在表2中的y连接在x.id等于y.id到连接
从z在jointable.DefaultIfEmpty（）
select new
{
x.Field1，
x.Field2，
x.Field3，
Field4 = z == null？ 0：z.Field4
};


在你的情况下，我有点困惑，因为你似乎正在使用的实体关系Linq不匹配你的SQL隐含的;是这里的关系零或一，零或多，一对一等？具体来说，你是这样做的：

 从上下文中的h中。验证
在上下文中加入t。 .AuthenticationId等于t.Authentications.AuthenticationId 
  

但您的SQL暗示身份验证零或更多的授权子项，而不是相反，这将更像：

  .Athentications 
 from h.Authorizations.DefaultIfEmpty（）
  

如果你能给我们更好地了解数据模型以及希望从中获得哪些数据，我们可以更轻松地解释该查询在Linq中的外观。假设你的关系符合SQL所隐含的关系，你应该能够使用下面的Linq查询来得到你想要的结果：

  var query = from h in context.Authentications 
 from h.Authorizations.DefaultIfEmpty（）
 select new 
 {
 h.AuthenticationId，
 h.Username ，
 Permissions = t == null？ null：t.Permissions，
组织= t == null？ new EntitySet< Organization>（）：t.Organizations 
}; 
 
 var query2 =从查询中的x 
从x.organizations.DefaultIfEmpty（）中的o 
 select new 
 {
 AUTHENTICATION_ID =（short？ x.AuthenticationId，
 x.Username，
 x.Permissions，
 OrganizationIdentifier = o == null？ null：o.OrganizationIdentifier，
 OrganizationID = o == null？ （short？）null：o.OrganizationID 
}; 
  

Here is a SQL Query I want to convert to EF4.3

        command = database.GetSqlStringCommand(@"
                                select 
                                    H.AUTHENTICATION_ID, 
                                    USERNAME, 
                                    PERMISSIONS,
                                    ORGANIZATION_IDENTIFIER, 
                                    O.ORGANIZATION_ID 
                                from 
                                    AUTHENTICATION H 
                                        left join [AUTHORIZATION] T on H.AUTHENTICATION_ID=T.AUTHENTICATION_ID 
                                        join ORGANIZATION O on O.ORGANIZATION_ID = T.ORGANIZATION_ID
                                order by H.AUTHENTICATION_ID");

Here is the best LINQ I could come up with:

        var query = from h in context.Authentications
            join t in context.Authorizations on h.AuthenticationId equals t.Authentications.AuthenticationId 
            join o in context.Organizations on t.Organizations.OrganizationId equals o.OrganizationId
            orderby
            h.AuthenticationId
            select new
            { AUTHENTICATION_ID = (Int16?)h.AuthenticationId,
                h.Username,
                t.Permissions,
                o.OrganizationIdentifier,
                OrganizationID = (Int16?)o.OrganizationId
            };

I know i need to merge my first join (between Authorizations & Authentications) into, lets say x and apply DefaultIfEmpty but can't make out the syntax.

EDIT: Image for clarification:

Any help will be highly appreciated. Regards.

解决方案

The basic syntax for a "left join" in Linq is like this:

from x in table1
join y in table2 on x.id equals y.id into jointable
from z in jointable.DefaultIfEmpty()
select new
{
  x.Field1, 
  x.Field2,
  x.Field3,
  Field4 = z == null ? 0 : z.Field4
};

In your case, I'm a little confused because the entity relations you seem to be using in your Linq don't match the ones implied by your SQL; are the relationships here zero-or-one, zero-or-many, one-to-one, etc? Specifically, you're doing this:

from h in context.Authentications
join t in context.Authorizations on h.AuthenticationId equals t.Authentications.AuthenticationId

but your SQL implies that "Authentication" is the parent here with zero-or-more "Authorization" children, not the other way around, which would be more like:

from h in context.Authentications
from t in h.Authorizations.DefaultIfEmpty()

If you can give us a better idea of the data model and what data you expect to get out of it we can more easily explain how that query would look in Linq. Assuming that your relationships match what is implied by the SQL, you should be able to get what you want using the following Linq queries:

var query = from h in context.Authentications
            from t in h.Authorizations.DefaultIfEmpty()
            select new
            {
                h.AuthenticationId,
                h.Username,
                Permissions = t == null ? null : t.Permissions,
                Organizations = t == null ? new EntitySet<Organization>() : t.Organizations
            };

var query2 = from x in query
             from o in x.organizations.DefaultIfEmpty()
             select new
             {
                 AUTHENTICATION_ID = (short?)x.AuthenticationId,
                 x.Username,
                 x.Permissions,
                 OrganizationIdentifier = o == null ? null : o.OrganizationIdentifier,
                 OrganizationID = o == null ? (short?)null : o.OrganizationID 
             };

这篇关于将LEFT OUTER JOIN转换为Entity Framework的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！