CODEWITHSUNDEEP
c#linqjoin
Anders M.
Anders M.

Reputation: 199

join 3 tables in linq getting all entries from two tables

I have 3 tables

The product table holds different products from a project, and the update table holds updates made to various products and holds a reference to the user who did it.

Basically what I want is to have a query that returns all products (since products to projects is a many to one relation) ordered by the date they we're last updated by the user who is currently logged in.

This is my current query:

IEnumerable<ProjectProduct> list =
       from joined in
           (from product in db.GetTable<Product>()
            join project in db.GetTable<Project>()
            on product.ProjectId equals project.ID
            select new { product, project })
       join projectupd in db.GetTable<ProjectUpdate>()
       on joined.product.ID equals projectupd.ProductID
       where projectupd.CreatedBy == ParamUser
       orderby projectupd.LastUpdate
       select new ProjectProduct(joined.project, joined.product);

However, the result I'm getting is only the entries in the update table, and not all the existing products. I know that the "where" clause makes it only select the updates created by a specific user, so I'm on the right track, but I have tried a couple of things to make the query successful, without luck though.

Does anybody have a suggestion on how to get the desired result?

Upvotes: 1

Views: 1791

Answers (1)

Christoffer Lette
Christoffer Lette

Reputation: 14806

Here's an answer that's a little verbose, and it uses method-chain syntax, but I do think it does what your looking for:

var products = db.GetTable<Product>();
var projects = db.GetTable<Project>();
var projectUpdates = db.GetTable<ProjectUpdate>();

var latestProjectUpdatesForUser = projectUpdates
    .Where(x => x.CreatedBy == paramUser)
    .GroupBy(x => x.ProductId)
    .Select(g => g.OrderByDescending(x => x.LastUpdate).First());

var list = products
    .Join(
        projects,
        product => product.ProjectId,
        project => project.Id,
        (product, project) => new
            {
                Product = product,
                Project = project,
                Update = latestProjectUpdatesForUser.FirstOrDefault(u => u.ProductId == product.Id)
            }
    )
    .OrderByDescending(x => x.Update != null ? (DateTime?)x.Update.LastUpdate : null)
    .ThenBy(x => x.Project.Id)
    .ThenBy(x => x.Product.Id)
    .Select(x => new ProjectProduct { Project = x.Project, Product = x.Product});

It takes advantage of the fact that DateTime? is sortable and that null values end up last when using OrderByDescending.

Upvotes: 1

Related Questions