LINQ - 首先在EF代码中外连接到父类 [英] LINQ - outer join to parent class in EF code first

问题描述

我在这里缺少一些基本的东西。

我有以下EF代码优先实体：公司和调制解调器。公司可以有调制解调器列表。

  public class公司
 {
 public int ID {get;组; } 
 ... 
 public virtual ICollection<调制解调器>调制解调器{get; set：} 
} 
 
 
 public class Modem 
 {
 public int ID {get;组; } 
 ... 
 
} 
  

这会生成列 Company_ID 作为外键，位于调制解调器表。

我想要返回所有调制解调器，如果有的话，可以使用他们的公司ID。
在SQL中，我将执行以下操作：

  select * from Modem m 
 left outer join Company c在
 m.Company_ID = c.ID 
  

我熟悉做一个左外连接在LINQ中使用.DefaultIfEmpty（）在DB首先而不是代码优先。

我的混乱出现是我没有m.Company_ID可用在LINQ到执行加入，因为它是由EF自动生成的。

如何在LINQ中执行此连接？

解决方案

EF的真正美丽在于你不需要想到连接（好吧，大部分时间）。您所需要的是通过导航属性正确定义您的实体及其关系。例如，这里是一个类似于你的模型的定义：

  public class Company 
 {
 public int ID {get;组; } 
 public string Name {get;组; } 
 public virtual ICollection<调制解调器>调制解调器{get;组; } 
} 
 
 public class Modem 
 {
 public int ID {get;组; } 
 public string Name {get;组; } 
 public virtual Company Company {get;组; } 
} 
 
 public class MyDbContext：DbContext 
 {
 public DbSet< Company>公司{get;组; } 
 public DbSet<调制解调器>调制解调器{get;组; 
 protected override void OnModelCreating（DbModelBuilder modelBuilder）
 {
 modelBuilder.Entity< Company>（）
 .HasMany（company => company.Modems）
。 WithOptional（modem => modem.Company）
 .Map（action => action.MapKey（Company_ID））; 
 base.OnModelCreating（modelBuilder）; 
} 
} 
  

请注意 Company.Modems 和 Modem.Company 属性，以及关系的流畅配置，包括FK列的名称。

现在，如果我们执行以下

  using（var db = new MyDbContext（））
 {
 var query = db.Modems.Select（m => new {Modem = m，CompanyName = m.Company.Name}）; 
 var sqlQuery = query.ToString（）; 
} 
  

sqlQuery 包含

  SELECT 
 [Extent1]。[ID] AS [ID]，
 [Extent1]。 [Name] AS [Name]，
 [Extent1]。[Company_ID] AS [Company_ID]，
 [Extent2]。[Name] AS [Name1] 
 FROM [dbo] ] AS [Extent1] 
 LEFT OUTER JOIN [dbo]。[Companies] AS [Extent2] ON [Extent1]。[Company_ID] = [Extent2]。[ID] 
  

你去那里 - 希望你看到你正在要求的 LEFT OUTER p>

I am missing something fundamental here I believe.

I have the following EF code-first entities: Company and Modem. A company can have a list of modems.

 public class Company
    {
        public int ID { get; set; }
        ...
        public virtual ICollection<Modem> Modems { get; set:}
    }  


 public class Modem
    {
        public int ID { get; set; }
        ...

    }

This generates a column Company_ID as a foreign key, on the Modem table.

I want to return all modems, with their company ID, if available. In SQL I would do the following :

select * from Modem m
left outer join Company c on 
m.Company_ID = c.ID

I am familiar with doing a left outer join in LINQ using .DefaultIfEmpty() in DB-first rather than code-first.

Where my confusion arises is that I don't have m.Company_ID available in LINQ to do the join, as it is auto generated by EF.

How do I perform this join in LINQ?

解决方案

The real beauty of the EF is that you don't need to think of joins at all (well, most of the time). All you need is to properly define your entities and their relations via navigation properties. For instance, here is a definition of a model similar to yours:

public class Company
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Modem> Modems { get; set; }
}

public class Modem
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual Company Company { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<Company> Companies { get; set; }
    public DbSet<Modem> Modems { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Company>()
            .HasMany(company => company.Modems)
            .WithOptional(modem => modem.Company)
            .Map(action => action.MapKey("Company_ID"));
        base.OnModelCreating(modelBuilder);
    }
}

Note the Company.Modems and Modem.Company properties, and the fluent configuration of the relationship, including the name of the FK column.

Now, if we do the following

using (var db = new MyDbContext())
{
    var query = db.Modems.Select(m => new { Modem = m, CompanyName = m.Company.Name });
    var sqlQuery = query.ToString();
}

the sqlQuery variable contains

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Company_ID] AS [Company_ID], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Modems] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Companies] AS [Extent2] ON [Extent1].[Company_ID] = [Extent2].[ID]

And there you go - hope you see the LEFT OUTER join you were asking for.

这篇关于LINQ - 首先在EF代码中外连接到父类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！