LINQ中的条件连接 [英] Conditional join in LINQ

问题描述

所以linq有点问题.我希望联接是外部联接还是内部联接，这取决于是否在表上过滤了值

外部联接:

var query = (from tblA in dc.tblA
             join tblB in GetMyTable() on tblA.Ref equals tblB.RefA into joinedTblB
             from tblB in joinedTblB.DefaultIfEmpty()
             select tblA);

内部联接:

var query = (from tblA in dc.tblA
             join tblB in GetMyTable() on tblA.Ref equals tblB.RefA into joinedTblB
             from tblB in joinedTblB
             select tblA);

我想要的是在同一查询中将其组合起来，并通过检查某些条件来进行外部联接"或内部联接"，如下所示:

var query = (from tblA in dc.tblA
             join tblB in GetMyTable() on tblA.Ref equals tblB.RefA into joinedTblB
             from tblNEWB in ((checkCondition==false) ? joinedTblB.DefaultIfEmpty() : joinedTblB)
             select new {
                tblA.ValueA,
                tblNEWB.ValueB
             });

我希望这能解决问题，但是我收到一条错误消息:"InvalidOperationException:类型为'System.Collections.Generic.IEnumerable'1 [tblB]的成员访问'tblB'的'System.String ValueB'不合法" /p>

我在这里想念东西吗?

更新:

我想要的是一个外部联接，但是当在tlbB上设置条件时，linq并没有生成我希望的查询.打开SQL事件探查器会给出以下查询:

LEFT OUTER JOIN tblB ON tblA.Ref = tblB.REfA AND tlbB.Key = '100'

正确的查询应该是:

LEFT OUTER JOIN tblB ON tblA.Ref = tblB.RefA
WHERE tblB.Key = '100'

原因是我的GetMyTable函数将联接中的表设置为条件.

解决方案

我想要的是将其合并到同一查询中，并通过检查某些条件来进行OUTER JOIN或INNER JOIN

获得您所要的内容的第一步是要认识到需要两个不同的sql语句. LinqToSql不会将您的条件发送到数据库中，因此数据库可以根据值确定应该进行哪种连接.

第二步，切换到方法语法.这种语法可以根据条件进行组合.

第三步，就是放弃匿名类型.您必须做一些令人不愉快的事情，以声明那些查询构建所需的变量以及那些随处可见的变量.只需创建一个具有所需属性的类型并使用它即可.

public class AandB
{
  public A TheA {get;set;}
  public B TheB {get;set;}
}

IQueryable<A> queryA =  dc.TblA.AsQueryable();

IQueryable<AandB> queryAandB = null;

if (checkCondition)
{
  //inner join
  queryAandB = queryA
    .Join(
      GetMyTable(),
      a => a.Ref, b => b.RefA,
      (a, b) => new AandB() {TheA = a, TheB = b}
    );
}
else
{
  // left join
  queryAandB = queryA
    .GroupJoin(
      GetMyTable(),
      a => a.Ref, b => b.RefA,
      (a, g) => new {a, g}
    )
    .SelectMany(
      x => x.g.DefaultIfEmpty(),
      (x, b) => new AandB(){TheA = x.a, TheB = b}
    );
}

List<AandB> results = queryAandB.ToList();

So I'm having a little problem with linq. I want a join to be an OUTER JOIN or an INNER JOIN, depending on whether or not values are filtered on the table

OUTER JOIN:

var query = (from tblA in dc.tblA
             join tblB in GetMyTable() on tblA.Ref equals tblB.RefA into joinedTblB
             from tblB in joinedTblB.DefaultIfEmpty()
             select tblA);

INNER JOIN:

var query = (from tblA in dc.tblA
             join tblB in GetMyTable() on tblA.Ref equals tblB.RefA into joinedTblB
             from tblB in joinedTblB
             select tblA);

What I want is to combine this in the same query, and by checking some condition do either an OUTER JOIN or an INNER JOIN, something like this:

var query = (from tblA in dc.tblA
             join tblB in GetMyTable() on tblA.Ref equals tblB.RefA into joinedTblB
             from tblNEWB in ((checkCondition==false) ? joinedTblB.DefaultIfEmpty() : joinedTblB)
             select new {
                tblA.ValueA,
                tblNEWB.ValueB
             });

I was hoping this would work, but I get an error saying "InvalidOperationException: Member access 'System.String ValueB' of 'tblB' not legal on type 'System.Collections.Generic.IEnumerable'1 [tblB]"

Am I missing something here?

UPDATE:

What I want is an outer join, but linq did not produce the query I hoped for, when conditions are set on tlbB. Turning on SQL Profiler gives this query:

LEFT OUTER JOIN tblB ON tblA.Ref = tblB.REfA AND tlbB.Key = '100'

While the correct query should be:

LEFT OUTER JOIN tblB ON tblA.Ref = tblB.RefA
WHERE tblB.Key = '100'

The reason for this is my GetMyTable-function that sets condition to the table in the join.

解决方案

What I want is to combine this in the same query, and by checking some condition do either an OUTER JOIN or an INNER JOIN

The first step to getting what you are asking for is to recognize that two distinct sql statements are required. LinqToSql is not going to send your condition into the database so the database can figure out what kind of join should happen based on a value.

The second step, is to switch to the method syntax. This syntax is more composable by conditions.

The third step, is to abandon anonymous types. You have to do unpleasant things to declare the variables you need for query construction with those running around. Just create a type with the properties you need and use that.

public class AandB
{
  public A TheA {get;set;}
  public B TheB {get;set;}
}

IQueryable<A> queryA =  dc.TblA.AsQueryable();

IQueryable<AandB> queryAandB = null;

if (checkCondition)
{
  //inner join
  queryAandB = queryA
    .Join(
      GetMyTable(),
      a => a.Ref, b => b.RefA,
      (a, b) => new AandB() {TheA = a, TheB = b}
    );
}
else
{
  // left join
  queryAandB = queryA
    .GroupJoin(
      GetMyTable(),
      a => a.Ref, b => b.RefA,
      (a, g) => new {a, g}
    )
    .SelectMany(
      x => x.g.DefaultIfEmpty(),
      (x, b) => new AandB(){TheA = x.a, TheB = b}
    );
}

List<AandB> results = queryAandB.ToList();

这篇关于LINQ中的条件连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！