none
linq 两表查询左外连接用DefaultIfEmpty ,却生成了内连接inner join语句 RRS feed

  • 问题

  •     各位大侠,我接触linq不多,现有两个表要实现左连接查询,网上搜了很多都说左连接用DefaultIfEmpty来实现,但自己亲自试的时候却总是生成了inner join查询语句,弄了几个小时都还是一样的结果,很纳闷,希望高手们来看看这个问题到底出在哪里,代码如下:
     var res = from s in db.Sales
                          join m in db.Movies on s.MovieID equals m.ID into all
                          from a in all.DefaultIfEmpty()  //这里的a可以写成m,结果一样
                          select new
                          {
                              ID = s.ID,
                              BuyDate = s.BuyDate,
                              BuyNumber = s.BuyNumber,
                              MovieID = s.MovieID,
                              TotalPrice = s.TotalPrice,
                              UserID = s.UserID,
                              movieName = a == null ? "aaa" : a.Title
                           };
    生成的SQL如下:
    {SELECT
    [Extent1].[ID] AS [ID],
    [Extent1].[BuyDate] AS [BuyDate],
    [Extent1].[BuyNumber] AS [BuyNumber],
    [Extent1].[MovieID] AS [MovieID],
    [Extent1].[TotalPrice] AS [TotalPrice],
    [Extent1].[UserID] AS [UserID],
    [Extent2].[Title] AS [Title]
    FROM  [dbo].[Sales] AS [Extent1]
    INNER JOIN [dbo].[Movies] AS [Extent2] ON [Extent1].[MovieID] = [Extent2].[ID]}

    唉,一直找不到原因,我只想要个最简单的像sql里面的left join也搞了这么久,高手帮看看,谢了。

    2014年8月21日 15:25

全部回复

  • Hello,

    你的问题确实很奇怪,因为我参照你的代码写了个查询,但是他的确是一个left out join

    这个是我的查询LINQ语句:

    var res = (from s in db.Orders
    
                               join m in db.OrderDetails on s.OrderID equals m.OrderID into all
    
                               from a in all.DefaultIfEmpty()
    
                               select new
    
                                {
    
                                    Order = s.OrderID,
    
                                    OrderDetailID = a == null ? "" : a.OrderDetailID
    
                                }).ToList();
    

    基本上和你的是一样的,然后这个是产生的TSQL:

    可以看到是left out join.展开查询结果,也可以看到如果外链的表不存在记录会表示成“”

    我想知道你的数据库是什么数据库,我用的是SQL ServerproviderEntity Framework 6。如果你用的是同样数据库,你可以上传你的程序给我们测试下是不是也会产生inner join. 如果你数据库provider是其他的, 那我建议你可以去你用的数据库论坛问下。

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    2014年8月22日 3:41
    版主
  • 我再仔细的检查了一下,出现如上问题我用的是code first 是按网上一个教程做的,于是在这个项目中我又新建了个edmx,linq代码一样,发现code first生成的是inner join ,edmx生成的确是 left join,比对了代码也没什么不同,都是继承DbContext,只不过一个是手写,一个是edmx生成的,具体细节也不找了,估计是手写的时候是哪里没配制对吧。

    2014年8月22日 14:17
  • Hello,

    可以分享下那个教程的连接吗? 根据你的描述,Code First 也是表现left out join,不清楚你是如何配置的,你可以试下这个:

    class Sample20140825
    
        {
    
            internal void Execute()
    
            {
    
                           using (DFDBEntities db =new DFDBEntities())
    
                {
    
                    db.Database.CreateIfNotExists();
    
    
                    db.Database.Log = Console.Write;
    
    
                    var res = (from s in db.Orders
    
    
                               join m in db.OrderDetails on s.OrderID equals m.OrderID into all
    
    
                               from a in all.DefaultIfEmpty()
    
    
                               select new
    
    
                                {
    
    
                                    Order = s.OrderID,
    
    
                                    OrderDetailID = a == null ? 0 : a.OrderDetailID
    
    
                                }).ToList();
    
                }
    
            }
    
        }
    
    public partial class DFDBEntities : DbContext
    
        {
    
    
            public virtual DbSet<Order> Orders { get; set; }
    
            public virtual DbSet<OrderDetail> OrderDetails { get; set; }
    
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
    
            {
    
                modelBuilder.Entity<Order>().HasMany(o => o.OrderDetails).WithRequired(o => o.Order).HasForeignKey(od => od.OrderID);
    
            }
    
        }
    
    
        public partial class Order
    
        {
    
            public Order()
    
            {
    
                this.OrderDetails = new HashSet<OrderDetail>();
    
            }
    
    
            public int OrderID { get; set; }
    
            public string OrderName { get; set; }
    
            public virtual ICollection<OrderDetail> OrderDetails { get; set; }
    
        }
    
        public partial class OrderDetail
    
        {
    
            public int OrderDetailID { get; set; }
    
            public string OrderDetailName { get; set; }
    
            public int OrderID { get; set; }
    
            public virtual Order Order { get; set; }
    
        }
    


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    2014年8月25日 7:33
    版主