Linq Method Syntax For Multiple Left Join

Three tables are needed to be joined together. Table [Package] ID (int) ContainerID (int) Code (string) Code2 (string) Table [UserHasPackages] UserID (Comes from Identity table

Solution 1:

I'm assuming that you want to join the UserHasPackages table because you wanted to filter the results for a specific user (I just put in a 'SomeUser' because I'm not sure where the 'UserHasPackages.ApplicationUserId' came from) since it is not included on the view model.

I believe something like the following should work:

var list = context.Packages
    .Join(context.Containers, p => p.ContainerID, c => c.ID, (p, c) =>new { p, c })
    .Join(context.UserHasPackages, pc => pc.p.ID, u => u.PackageID, (pc, u) =>new { pc.p, pc.c, u })
    .Where(pcu => pcu.u.UserID == "SomeUser")
    .Select(pcu =>new
    {
        pcu.p.ID,
        pcu.c.Name,
        pcu.p.Code,
        pcu.p.Code2
    });

You could also do this using the query syntax:

var query = from package in context.Packages
            join container in context.Containers on package.ContainerID equals container.ID
            join userHasPackage in context.UserHasPackages on package.ID equals userHasPackage.PackageID
            where userHasPackage.UserID == "SomeUser"selectnew
            {
                package.ID,
                container.Name,
                package.Code,
                package.Code2
            };