将复杂的 T-SQL 查询转换为 LINQ to Entities

本文关键字：LINQ to Entities 转换 查询 复杂 T-SQL | 更新日期: 2023-09-27 18:31:18

这是产生正确结果的SQL代码：

select s.Code, s.Name, coalesce(ss.Url,  a.Url), a.SocialMediaTypeKey
from School s
Left join 
(
SELECT dbo.SchoolSocialMedia.SocialMediaTypeKey
, SchoolSocialMedia.Url
, dbo.Department.Name
, dbo.Department.ImportBusinessKey
FROM dbo.SchoolSocialMedia 
    INNER JOIN dbo.Department ON dbo.SchoolSocialMedia.DepartmentId =   dbo.Department.Id
) A 
ON 1 = 1
Left join dbo.SchoolSocialMedia ss ON ss.SchoolId = s.Id and    ss.SocialMediaTypeKey = a.SocialMediaTypeKey
where  s.[DeactivatedDate] is null

这就是我在 C# 中取得的成就，但它没有产生正确的结果 - 事实上，它返回的结果为零：

        var departmentSocialMediaResult =
            from ssm in context.SchoolSocialMedia
            from d in context.Department.Where(d => d.Id == ssm.DepartmentId)
            select new { ssm.SocialMediaTypeKey,
                ssm.Url,
                d.Name,
                ssm.SchoolId };
        var result =
            (from s in context.School
             from ssm in context.SchoolSocialMedia.DefaultIfEmpty()
             from dssm in departmentSocialMediaResult.DefaultIfEmpty()
             .Where(dssm => dssm.SchoolId == s.Id && dssm.SocialMediaTypeKey == ssm.SocialMediaTypeKey)
             select new { ssm.SchoolId, ssm.SocialMediaTypeKey, ssm.Url })
             .ToDictionary(ssm => new SchoolSocialMediaKey(
                    ssm.SchoolId, ssm.SocialMediaTypeKey),
                ssm => ssm.Url);

有没有人对如何更好地将 T-SQL 转换为 LINQ 到实体有任何建议？ 我做错了什么？ 蒂亚。

更新：

谢谢你，@Aducci，你的回答是正确的答案。 由于结果被放入字典中，这就是我最终使用的：

        var query =
            (from s in context.School
            from a in
            (
               from ssm in context.SchoolSocialMedia
               join d in context.Department on ssm.DepartmentId equals d.Id
               select new
               {
                   ssm.SocialMediaTypeKey,
                   ssm.Url,
                   d.Name
               }
            ).DefaultIfEmpty()
            from ss in context.SchoolSocialMedia
                              .Where(x => s.Id == x.SchoolId)
                              .Where(x => a.SocialMediaTypeKey == x.SocialMediaTypeKey)
                              .DefaultIfEmpty()
            select new
            {
                ss.SchoolId,
                Url = ss.Url ?? a.Url,
                a.SocialMediaTypeKey
            }).Distinct();
        return
            query
            .ToDictionary(
                ssm => new SchoolSocialMediaKey(
                    ssm.SchoolId, ssm.SocialMediaTypeKey),
                ssm => ssm.Url);

将复杂的 T-SQL 查询转换为 LINQ to Entities

我确信有更好的方法来编写原始查询，但我没有花太多时间进行分析，而是将其转换为 linq。通常，linq 查询应具有与 tsql 查询相同的结构，如下所示：

var query =
   from s in context.School
   from a in
   (
      from ssm in context.SchoolSocialMedia
      join d in context.Department on ssm.DepartmentId equals d.Id
      select new
      {
         ssm.SocialMediaTypeKey,
         ssm.Url,
         d.Name,
         d.ImportBusinessKey
      }
   ).DefaultIfEmpty()
   from ss in context.SchoolSocialMedia
                     .Where(x => s.Id == x.SchoolId)
                     .Where(x => a.SocialMediaTypeKey  == x.SocialMediaTypeKey)
                     .DefaultIfEmpty()
   select new
   {
       s.Code, 
       s.Name, 
       Url = ss.Url ?? a.Url, 
       a.SocialMediaTypeKey
   };
相关文章：