Songaila Songaila - 1 month ago 4
C# Question

How to join tables using LINQ and navigation properties

Previous question link

Consider to my previous question (I put a link to it) I need to get some different information.

Here is a DB structure I only added navigation property

public virtual ICollection<Accident> Accidents { get; set; }


to Transport class

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 virtual ICollection<Accident> Accidents { 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; }
}
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; }
}


If I want to get all accidents I use

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


What would be a code if I would like to select TransportObject with added specific Accident data

public class TransportObject
{
[Key]
public int TransportID { get; set; }
public string Model { get; set; }
public string Brand { get; set; }
public int AccidentID { get; set; }
public DateTime AccidentDate { get; set; }
}

Answer

Use this code

var transports = DBContext.Transports
        .SelectMany(
            x => x.Accidents, 
            (t, a) => new TransportObject 
            {
                TransportID = t.TransportID,
                Model = t.Model,
                Brand = t.Brand,
                AccidentID = a.AccidentId,
                AccidentDate = a.AccidentDate
            }
        );

More about select many in MSDN.