CODEWITHSUNDEEP
c#wpflinq
Hucky
Hucky

Reputation: 29

Linq select five tables down

I have five tables which are related like

Chemicals -> Ingredients -> Solutions -> Usages -> Projects

on a SQL-Server, I want to retrieve data Projects that are related to Chemicals, and some data of the intermediate tables. I can do it with the following query (at least in LinqPad):

from c in Chemicals
    join i in Ingredients on c.Chem_ID equals i.Chem_ID into TempTab01
    from tt01 in TempTab01.DefaultIfEmpty()
        join s in Solutions on tt01.Sol_ID equals s.Sol_ID into TempTab02
            from tt02 in TempTab02.DefaultIfEmpty()
                join u in Usages on tt02.Sol_ID equals u.Sol_ID into TempTab03
                    from tt03 in TempTab03.DefaultIfEmpty()
                        join pro in Projects on tt03.Study_ID equals pro.Study_ID into TempTab04
                        from tt04 in TempTab04.DefaultIfEmpty()
select new
{
c_ChemID = c.Chem_ID,
c_Name = c.Name, 
i_ChemID = (int?)tt01.Chem_ID,
i_Sol_id = (int?)tt01.Sol_ID,
i_IngredID = (int?)tt01.Ingred_ID,
tt01.Amount,
s_SolID = (int?)tt02.Sol_ID,
s_SolName = tt02.SolName,
u_SolID = (int?)tt03.Sol_ID,
u_StudyID = (int?)tt03.Study_ID,
pro_StudyID = (int?)tt04.Study_ID,
pro_StudyNo = tt04.StudyNo,
pro_ProjectName = tt04.ProjectName
}

I do not want to use the joins, but Navigation properties instead, but I do not know how to use several Select or SelectMany to drill down to more than 2 tables.

I'm using EF6 to get the data from SQL Server, the edmx-class for Chemicals is e. g.

    public long Chem_ID { get; set; }
    public string Name { get; set; }

    public virtual Person Person { get; set; }
    public virtual Project Project { get; set; }

    public virtual ICollection<Ingredient> Ingredients { get; set; }
    public virtual ICollection<Usage> Usages { get; set; }

that is Chemicals has a 1:m relation to Ingredients, Ingredients to Solutions = 1:1, Solutions to Usages = 1 : m, and Usages to Projects = 1:1. Hope this answers your question.

Upvotes: 1

Views: 63

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205599

It would be nice if you have provided the class definitions with navigation properties (one downside of using joins is that the cardinality is not quite clear).

But the principle is simple. For reference type navigation property you use let clause, for collection type - from (optionally appending DefaultIfEmpty() if left outer join semantics are needed).

So assuming your model is like this:

class Chemical
{
    public ICollection<Ingredient> Ingredients { get; set; }
}

class Ingredient
{
    public Chemical Chemical { get; set; }
    public Solution Solution { get; set; }
}

class Solution
{
    public ICollection<Ingredient> Ingredients { get; set; }
    public ICollection<Usage> Usages { get; set; }
}

class Usage
{
    public Solution Solution { get; set; }
    public Project Project { get; set; }
}

class Project
{
    public ICollection<Usage> Usages { get; set; }
}

then the query would be like this:

from c in db.Chemicals
from i in c.Ingredients.DefaultIfEmpty()
let s = i.Solution
from u in s.Usages.DefaultIfEmpty()
let p = u.Project
...

Upvotes: 2

Related Questions