实体框架 - 加入视图 [英] Entity framework - right join to a view

问题描述

我的数据库环境中有2个实体：

员工

EmployeeHolidayEntitlement

我认为这是一个相当正常的一对一的关系，但员工可以存在没有EmployeeHolidayEntitlement，但是EmployeeHolidayEntitlement不存在没有员工

员工被映射到我的数据库中的视图

EmployeeHolidayEntitlement是一个表

我的类是：

EmployeeHolidayEntitlement

  [Table tblEmployeeHolidayEntitlement）] 
 public class EmployeeHolidayEntitlement 
 {
 [Key] 
 public int EmployeeNumber {get;组; } 
 
 public virtual Employee Employee {get;组; } 
 
 public decimal StandardEntitlement {get;组; 
 
 //为简洁起见
 
  

员工

  [Table（vEmployee）] //注意v  - 查看
 public class Employee 
 {
 [Key] 
 public int EmployeeNumber {get;组; } 
 
 public string FirstName {get;组; } 
 public string LastName {get;组; } 
} 
  

在构建我的上下文时，我做：

（不确定这是否正确！）

  modelBuilder.Entity< EmployeeHolidayEntitlement>（）
 .HasRequired（w = > w.Employee）
 .WithOptional（）; 
  

当查询时，如果可能，我想要为每个员工一个EmployeeEntitlement记录（天气存在于tblEmployeeHolidayEntitlement或者不） -

我的查询当前如下所示：

 从
 userEntitlement在db.ADUserHolidayEntitlement 
加入
 adUser在db.ADUsers 
 
 userEntitlment.EmployeeNumber等于adUser.EmployeeNumber 
选择userEntitlement 
  

但这是（我认为）做一个LEFT加入 -
只返回有两个实体tblEmployeeHolidayEntitlement中的条目

我想像得到的SQL需要看起来像：

  SELECT 
 employee.EmployeeNumber，
 employeeHol。* 
 
 FROM tblEmployeeHolidayEntitlement employeeHol 
 
 RIGHT JOIN vEmployee employee 
 ON 
 employeeHol.EmployeeNumber = employee.EmployeeNumber 
  

这是否可能？

解决方案

您的代码正在进行内部连接。我相信您要求检索所有员工，每个员工（如果存在）都需要一个 EmployeeHolidayEntitlement 。

要执行左连接，请使用类似于以下内容的查询：

 从db.ADUsers中的adUser 
加入userEntitlement在db.ADUserHolidayEntitlement on 
 adUser.EmployeeNumber等于userEntitlment.EmployeeNumber到g.DefaultIfEmpty（）中的userEntitlement中的g 
 
选择新
 {
 adUser，
 userEntitlement //将为空的无权利存在
} 
  

I have 2 entities in my db context:

Employee
EmployeeHolidayEntitlement

I think it's a fairly normal one-one relationship - but Employee can exist without EmployeeHolidayEntitlement, but EmployeeHolidayEntitlement cannot exist without Employee

Employee is mapped to a view in my database
EmployeeHolidayEntitlement is a table

My classes are:

EmployeeHolidayEntitlement

[Table("tblEmployeeHolidayEntitlement")]
public class EmployeeHolidayEntitlement
{
    [Key]
    public int EmployeeNumber { get; set; }

    public virtual Employee Employee { get; set; }

    public decimal StandardEntitlement { get; set; }

    //.....omitted for brevity
}

Employee

[Table("vEmployee")] //note v - it's a view
public class Employee
{
    [Key]
    public int EmployeeNumber { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
}

When building my context, I do:
(not sure if this is correct!)

modelBuilder.Entity<EmployeeHolidayEntitlement>()
  .HasRequired(w => w.Employee)
  .WithOptional();

When querying, if possible, I would like one EmployeeEntitlement record for each Employee (weather it exists in tblEmployeeHolidayEntitlement or not) -

My query currently looks like this:

from
    userEntitlement in db.ADUserHolidayEntitlement
 join
    adUser in db.ADUsers
    on
    userEntitlment.EmployeeNumber equals adUser.EmployeeNumber
select userEntitlement

But this is (i think) doing a LEFT join - It's only returning the 2 entities that have an entry in tblEmployeeHolidayEntitlement

I would imagine the resultant SQL needs to look something like:

SELECT 
employee.EmployeeNumber, 
employeeHol.* 

FROM tblEmployeeHolidayEntitlement employeeHol

RIGHT JOIN vEmployee employee
ON 
employeeHol.EmployeeNumber = employee.EmployeeNumber

Is this even possible?

解决方案

Your code is doing an inner join. I believe you're asking to retrieve all employees, and an EmployeeHolidayEntitlement for each employee (if it exists).

To perform a left join, use a query similar to this:

from adUser in db.ADUsers
join userEntitlement in db.ADUserHolidayEntitlement on
    adUser.EmployeeNumber equals userEntitlment.EmployeeNumber into g
from userEntitlement in g.DefaultIfEmpty()
select new 
{
    adUser, 
    userEntitlement // Will be null of no entitlement exists
}

这篇关于实体框架 - 加入视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！