通过带有Where子句的实体框架通过Linq To Entities进行左外连接

Question

我已阅读与使用实体框架时在 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 查询是通过实体框架的 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();