CODEWITHSUNDEEP
c#entity-frameworklinq
Songaila
Songaila

Reputation: 219

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; }
}

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

First I created new class which 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; }
 }

Could you please give me examples:

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

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

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?

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??

Considering to 2 part I wrote:

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

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

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

Upvotes: 1

Views: 555

Answers (2)

Richard
Richard

Reputation: 109005

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();

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.

Upvotes: 1

Chad McGrath
Chad McGrath

Reputation: 1601

var accidents = DBContext.Accidents.Select( a => new AccidentObject 
{ 
    AccidentID = a.AccidentId,
    AccidentDate 
    TransportID 
    Model 
    Brand = a.Transport.Brand,
    PersonID = a.Person.PersonID,
    FirstName 
    LastName 
});

and fill in the blanks in much the same way. here's a linq example without using lambda expressions, that includes a group by clause if you prefer it: Linq to sql select into a new class

Upvotes: 1

Related Questions