如何使用LINQ to Entities查询来连接表？ [英] How to join tables using LINQ to Entities query?

问题描述

我有一个简单的数据库结构：

I have a simple db structure:

public class Person
{
    [Key]
    public int PersonID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Transport
{
    [Key]
    public int TransportID { get; set; }
    public string Model { get; set; }
    public string Brand { get; set; }
}

public class Accident
{
    [Key]
    public int AccsidentID { get; set; }
    public DateTime AccidentDate { get; set; }
    public int TransportID { get; set; }
    [ForeignKey("TransportID")]
    public virtual Transport Transport { get; set; }

    public int PersonID { get; set; }
    [ForeignKey("PersonID")]
    public virtual Person Person { get; set; }
}

我需要创建一个事故列表，我可以传递给WPF表单（使用MVVM）

I need to create a list of accidents, wich I could pass to WPF form (using MVVM)

首先我创建了一个新的类女巫，我想在我的GridControl中看到

First I created new class witch I would like to see in my GridControl

public class AccsidentObject
{
    [Key]
    public int AccidentID { get; set; }
    public DateTime AccidentDate { get; set; }
    public int TransportID { get; set; }
    public string Model { get; set; }
    public string Brand { get; set; }
    public int PersonID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
 }

请问你可以举个例子：

1. 如果我想从事故表中获取所有值的列表，包括来自运输和人员表的数据

1. if I want to get list of all values from Accident table including data from Transport and Person tables

如果我想获得按TransportID分组的事故列表（也包括来自个人和运输表的数据）

if I want to get Accident list grouped by TransportID (also include data from Person and Transport tables)

我从Linq查询获取数据：

I am getting data from Linq query:

var result = from ac in DBContext.Accidents select ac;
List<Accident> accidentList = result.toList();

但是，我需要添加一些字段来从其他表中列出什么是代码？

But I need to add some fields to list from other tables, what would be a code?

我做错了什么，不能构造一个AccidentObject列表，也许在我的DBContext中有一些错误，列出了o ...可以请帮我了解列表元素

What do I do wrong and could not construct a list of AccidentObject, maybe there are some mistake in my DBContext, lists o something... Could you please help me to understand List elements??

考虑到2部分，我写道：

Considering to 2 part I wrote:

var result = from DB in DBContext。事件选择ac;

var result = from ac in DBContext.Accidents select ac;

result = result.GroupBy（g => g.TransportID）.toList（）;

result = result.GroupBy(g => g.TransportID).toList();

现在我需要添加一些传输细节和格式的AccsidentObject列表...

And now I need to add some Transport details and format AccsidentObject list ...

推荐答案

要获取一个实体的实体）使用 Include 扩展方法，或者包含在您的类型的最终投影中：

To get an entity (or collection of entities) with associations eagerly populated use the Include extension method, or include in a final projection into your type:

var res = await (from a in ctx.Accidents
                 select new AccsidentObject {
                    AccidentID = a.AccidentID,
                    TransportID = a.Transport.TransportID,
                    Model = a.Transport.Model,
                    // …
                 }).ToListAsync();

您可以在LINQ理解中使用 groupby 表达给某个群体。在结果中， Key 属性是分组的东西，每个实例都是按所有分组的集合。

You can use groupby in a LINQ comprehension expression to group by something. In the result the Key property is the thing grouped by and each instance is a collection of all things grouped by.

var res = await (from a in ctx.Accidents
                 group by a.TransportID into g
                 select new {
                   TransportID = g.Key,
                   Accidents = g
                 }).ToListAsync();

在结果匿名类型中，事件属性收集事故。

In the resulting anonymous types the Accidents property with be a collection of Accident.

这篇关于如何使用LINQ to Entities查询来连接表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！