entity-framework - 通过带有 Where 子句的 Entity Framework 通过 Linq To Entities 进行左外连接

Question

我已阅读与使用 Entity Framework 时在 Linq to Entities (.NET 3.5) 中实现等效的 LEFT OUTER JOIN 相关的所有帖子，但尚未找到解决以下问题的方法。

给定两个表，由以下对象表示:

    public class Foo
    {
        public int FooId;  // primary key
        public string Name;
    }

    public class Bar
    {
        public int BarId;  // primary key
        public int FooId;  // foreign key
        public string Desc;
    }

我需要创建一个 Linq to Entities 语句，它与以下 SQL 语句等效。请注意，WHERE 语句包含两个跨越两个表的 OR 条件，以及 DISTINCT 限定符的使用。

SELECT DISTINCT
    Foo.*
FROM
    Foo
    LEFT OUTER JOIN Bar ON Foo.FooId = Bar.FooId
WHERE
    (Foo.Name = 'fooname' OR Bar.Desc = 'bardesc')

我生成的 Linq 查询是通过 Entity Framework 的 Linq to Entities，并将(希望)生成要在服务器上执行的单个 SQL 语句。 Linq to Entities 不支持 FirstOrDefault() 扩展子句，因此 LEFT OUTER JOIN 的标准 Linq 语法将不起作用。

这是我目前拥有的解决方案，但我无法执行以下任一操作:

1) 生成一个结果集，其中包含将由 LEFT OUTER JOIN 操作返回的一组 Foo/Bar 组合。

2) 实现等价的 WHERE 子句: WHERE (Foo.Name = 'fooname' OR Bar.Desc = 'bardesc')

    private class JoinSet
    {
        public Foo Foo;
        public IQueryable<Bar> Bars;
    };

    private class FooBar
    {
        public Foo Foo;
        public Bar Bar;
    };

    IEnumerable<Foo> OuterJoinTest()
    {
        IQueryable<Foo> fooBaseQuery = dbContext.FooSet;
        IQueryable<Bar> barBaseQuery = dbDontext.BarSet;

        IQueryable<JoinSet> joinQuery =
            from foo in fooBaseQuery
            select new JoinSet
                    {
                        Foo = foo,
                        Bars = barBaseQuery.Where(bar => bar.FooId == foo.FooId)
                    };

        // How do I generate a result set containing FooBar objects ?

        // How or where do I insert the equivalent of: ?
        //  WHERE (Foo.Name = 'fooname' OR Bar.Description = 'bardesc')

        IQueryable<Foo> resultQuery =
            from joinSet in joinQuery
            select joinSet.Foo;

        resultQuery = resultQuery.Distinct();

        return resultQuery.ToList();
    }

任何帮助、想法或建议将不胜感激。

欧拉运算符

Answer 1

.NET 3.5

private class FooBar
{
   public Foo Foo { get; set; }
   public Bar? Bar { get; set; }
}

var innerQuery = from foo in context.Foos
                 from bar in context.Bars
                 where foo.Name == 'fooname' || bar.Description == 'bardesc'
                 where foo.FooId == bar.FooId
                 select new FooBar { Foo = foo, Bar = bar };


var outerQuery = from foo in context.Foos
                 where foo.Name == 'fooname' 
                 where !context.Bars.Any(b => b.FooId == foo.FooId)
                 select new FooBar { Foo = foo, Bar = null };

var leftouterjoinQuery = innerQuery.Union(outerQuery).Distinct();

.NET 4.0

var query = (from foo in context.Foo
            join b in context.Bar
            on foo.FooId equals b.FooId into Bar
            from bar in Bar.DefaultIfEmpty()
            where foo.Name = 'fooname' || bar.Description = 'bardesc'
            select new { foo, bar }).Distinct();