LINQ有条件添加连接

本文关键字：连接 添加 有条件 LINQ | 更新日期: 2023-09-27 18:04:00

我有一个LINQ查询，我试图从2个表返回数据，但我加入的表是有条件的。

这就是我想做的:

if (teamType == "A"){
    var query = from foo in context.People
                join foo2 in context.PeopleExtendedInfoA
                select foo;
}
else {
    var query = from foo in context.People
                join foo2 in context.PeopleExtendedInfoB
                select foo;
}

然后我进一步过滤查询。我显然不能这样设置它，因为我将无法访问if块之外的"query"，但它显示了我要做的事情。这是我稍后试图用查询做的一个例子:

if (state != null)
{
     query = query.Where(p => p.State == state);
}
if (query != null) {
   var queryFinal = from foo in query
         select new PeopleGrid()
         {
              Name = foo.Name,
              Address = foo.Address,
              Hobby = foo2.Hobby
         }
}

我要返回的是表foo中的所有数据，然后是连接表中的一个字段，但根据逻辑，连接表会有所不同。peopleextendeddinfoa和peopleextendeddinfob都有列'Hobby'，但我没有办法从连接表访问'Hobby'，这是我需要从连接表的唯一字段。

我该怎么做呢?

LINQ有条件添加连接

PeopleExtendedInfoA和PeopleExtendedInfoB继承自相同的基类吗?您可以创建一个IQueryable<BaseClass>，并在添加连接时让linq提供程序为您解决它。示例:

IQueryable<BasePeople> basePeople;
if (teamType == "A")
   basePeople = context.PeopleExtendedInfoA;
else
   basePeople = context.PeopleExtendedInfoB;
var query = from foo in context.People
            join foo2 in basePeople on foo.Id equals foo2.PeopleId
            select new PeopleGrid()
            {
              Name = foo.Name,
              Address = foo.Address,
              Hobby = foo2.Hobby
            };

试试:

 var queryFinal = from foo in query
                  where foo.State == state !=null ? state : foo.State
         select new PeopleGrid()
         {
              Name = foo.Name,
              Address = foo.Address,
              Hobby = foo2.Hobby
         }

您可以查询到包含相关字段的中间类型，或者如果查询足够简单，您可以使用如下所示的匿名类型。重要的部分是Join调用都具有相同的返回类型({ p.Name, a.Hobby }和{ p.Name, b.Hobby }都将是相同的匿名类型)。

var baseQuery = context.People;
var joined = type == "A" ?
    baseQuery.Join(PeopleExtendedInfoA,
        p => p.Id,
        a => a.PeopleId,
        (p, a) => new { p, a.Hobby }) :
    baseQuery.Join(PeopleExtendedInfoB,
        p => p.Id,
        b => b.PeopleId,
        (p, b) => new { p, b.Hobby });
var result = joined.Select(x => new
    {
        x.p.Name,
        x.p.Address,
        // etc.
        x.Hobby
    };

我明白了。感谢所有人的回复，它让我的大脑再次工作，并给了我一些新的想法(即使我没有直接接受他们中的任何一个)。我意识到我需要在最后而不是开始做连接，这样我就不必处理不同类型的过滤。我是这样做的:

var query = from foo in context.People
            select foo;
if (state != null)
{
    query = query.Where(p => p.State == state);
}
if (query != null) {
   if (teamType == "A")
   {
      var queryFinal = from foo in query
                       join foo2 in context.PeopleExtendedInfoA
      select new PeopleGrid()
      {
          Name = foo.Name,
          Address = foo.Address,
          Hobby = foo2.Hobby
      }
   }
   else
   {
      var queryFinal = from foo in query
                       join foo2 in context.PeopleExtendedInfoB
      select new PeopleGrid()
      {
          Name = foo.Name,
          Address = foo.Address,
          Hobby = foo2.Hobby
      }
   }
}
相关文章：