Converting Linq Expression to C# EF

Question

I am not very good at Linq expressions so please forgive the simplicity. I have two related tables and writing a join in TSQL would be easy but I am using Entity Framework and Linq.

I hacked out a query in LINQPad (via reading articles and tutorials) and it is returning the results as I want finally.

Now I need to turn that into C# code. I have google for translator site and find plenty of SQL to Linq but none for what I am doing, possibly because I don't know that I am using the correct search terms.

My LINQPad code is this:

DashboardNavigations
.OrderBy(x => x.Id)
.Select(x => new 
{
    x.Id,
    x.Name,
    x.Description,
    x.ImageUrl,
    SubDash = SubDashNavs 
        .Where(s => s.DashId == x.Id)
        .Select(y => new { y.Id, y.Name, y.Description })
    
})

I am used to writing SIMPLE Linq expressions to entity framework like this:

var query = _mlsDashboardNavRepository.Table;
query = query.OrderBy(c => c.Id);
var dashnav = query.ToList();

As you can see I have the main records returning but can't get the join right. How can I proceed?

Answer 1

As stated in comments I suggest that your _mlsDashboardNavRepository only refers to DashboardNavigations since you say you retrieve your main records using only _mlsDashboardNavRepository.Tables; Where is your DashboardNavigations reference? To perform your query you need a context that refers to (all) your database tables. Below is code in LINQ format using a database context, that might be easier on you if you're used to SQL syntax.

var query = (from d in context.DashboardNavigations
             join s in context.SubDashNavs on d.Id equals s.Id
             orderby d.Id ascending
             select new 
             { 
                d.Id, 
                d.Name, 
                d.Description,
                d.ImageUrl,
                SubDash = s 
             });

var dashNavs = query.ToList();