左连接Linq查询

3 回答

• 3

  In reply to your update ，要创建空列表，您可以在 manySubObjects 的任务中执行三元组 .

  select new MyObject
  {
      field1= ga.Key.Field1,
      field2= ga.Key.Field2,
      manySubObjects =
          (from g in ga select g).FirstOrDefaut() == null ? null : 
          (from g in ga select new SubObject {
             fielda= g.fielda,
             fieldb= g.fieldb
          }).ToList()
  }).ToList();
  

  • 这是一个dotnetfiddle，试图做你正在尝试的事情 . https://dotnetfiddle.net/kGJVjE

  • 以下是基于您的评论的后续dotnetfiddle . https://dotnetfiddle.net/h2xd9O

  In reply to your comments ，上面的内容适用于Linq to Objects，但不适用于Linq to SQL . Linq to SQL会抱怨它，"Could not translate expression ... into SQL and could not treat as a local expression."那是因为Linq无法将自定义 new SubObject 构造函数转换为SQL . 为此，您必须编写更多代码以支持转换为SQL . 见Custom Method in LINQ to SQL query和this article .

  我想我们已经充分回答了你关于左连接的原始问题 . 考虑在Linq to SQL查询中询问有关使用自定义方法/构造函数的新问题 .

  回复于 2024-04-27T02:42:48+08:00
• 3

  我认为你想要的结果可以通过GroupJoin()给出

  下面的代码将生成一个类似的结构

  Field1，Field2，List <SubObject> null如果为空

  示例代码

  var query = dc.Table1.Where(x => Table1_id == id).OrderBy(x => x.sort)
                  .GroupJoin(dc.Table2, (table1 => table1.Table1_id), (table2 => table2.Table2_id),
                      (table1, table2) => new MyObject 
                                          { 
                                              field1 = table1.Field1, 
                                              field2 = table1.Field2, 
                                              manySubObjects = (table2.Count() > 0) 
                                                  ? (from t in table2 select new SubObject { fielda = t.fielda, fieldb = t.fieldb}).ToList()
                                                  : null 
                                          }).ToList();
  

  Dotnetfiddle link

  UPDATE

  从你的评论我看到了这一点

  ga.Select(g = > new SubObject(){fielda = g.fielda, fieldb = g.fieldb})
  

  我认为应该是（取决于如何 Build “ga”）

  ga.Select(g => new SubObject {fielda = g.fielda, fieldb = g.fieldb})
  

  请使用整个查询更新您的问题，这将有助于解决问题 .

  **更新BIS **

  sentEmails = //ga.Count() < 1 ? null :
                //(from g in ga select g).FirstOrDefault() == null ? null :
               (from g in ga select new Email{
                    email_to = g.email_to,
                    email_from = g.email_from,
                    email_cc = g.email_cc,
                    email_bcc = g.email_bcc,
                    email_subject = g.email_subject,
                    email_body = g.email_body }).ToList()
  

  应该：

  sentEmails = //ga.Count() < 1 ? null :
               ((from g in ga select g).FirstOrDefault() == null) ? null :
               (from g in ga select new Email{
                    email_to = g.email_to,
                    email_from = g.email_from,
                    email_cc = g.email_cc,
                    email_bcc = g.email_bcc,
                    email_subject = g.email_subject,
                    email_body = g.email_body }).ToList()
  

  检查该组是否具有First，如果该组没有任何记录，则Time Stamp的Action.Name没有要发送的电子邮件 . 如果First不为null，则循环抛出组元素并创建Email列表，

  回复于 2024-04-27T02:42:48+08:00
• 0

  var results =
  (
      // Use from, from like so for the left join:
      from a in dc.Table1
      from e in dc.Table2
          // Join condition goes here
          .Where(a.Id == e.Id)
          // This is for the left join
          .DefaultIfEmpty()
      // Non-join conditions here
      where a.Id == id
      // Then group
      group by new
      {
          a.Field1,
          a.Field2
      }
  ).Select(g =>
      // Sort items within groups
      g.OrderBy(item => item.sortField)
      // Project required data only from each item
      .Select(item => new
      {
          item.FieldA,
          item.FieldB
      }))
  // Bring into memory
  .ToList();
  

  然后在内存中投影到非EF模型类型 .

  回复于 2024-04-27T02:42:48+08:00