LINQ how do I specify select certain columns in a one to many joins-CodePudding

I have a situation where I have a LINQ query. It has two joins (one to many) but it is bringing back all of the columns in the joined tables. I'm not sure how to create the LINQ query to only being back a few fields from the joined tables.

var data = from mc in ctx.MembershipChapters
           where mc.PartitionKey == controllerStateManager.PartitionKey && mc.MembershipId == membershipId
           join prd in ctx.Products on mc.ProductId 
           equals prd.Id into prods
           from prd in prods.DefaultIfEmpty()

           join oli in ctx.OrderLineItems on mc.OrderLineItemId equals oli.OrderLineItemId into olis
           from oli in olis.DefaultIfEmpty()
           select new
           {
             MembershipName = mc.Membership.Name,
             Products = prods.Select(p => new { 
ProductName = p.Name, ProductId = p.Id }),

OrderLineItems = olis.Select(o => new { OrderLineItemName = o.Description, OrderLineItemId = o.OrderLineItemId })
};
controllerStateManager.Data = data.ToList();

This does not work...I get an error: "o" is not in scope.

Basically the output should follow this:

MembershipChapter ---> OrderLineItems ----------> Products

I'm new to LINQ and I have been struggling on this for far too long.

CodePudding user response：

It is a little hard to read, but if the domain is linked correctly then I think you just want to end up with a query like this:

from ol in ctx.OrderLines where
    ol.MembershipChapter.PartitionKey == controllerStateManager.PartitionKey
    select new {ol.Whatever, ol.Product.Whatever};

CodePudding user response：

If you have a one-to-many relationship, and you want the "one" items, each one with its zero or more subitems, like Schools with their zero or more Students; Customers with their zero or more Orders, or, as in your case: MembershipChapters with their OrderLineItems, consider to use one of the overloads of Queryable.GroupJoin.

If you start on the "many" side, and you want each item with its one parent item, so you want the Student with the School he attends, or the Order with the one and only Customer who placed the order, use one of the overloads of Queryable.Join.

I almost use the overload that has a parameter resultSelector, so you can exactly define what should be in the result.

Requirement: given tables MembershipChapters, OrderLineItems and Products. There is a one-to-many relationship between MembershipChapters and OrderLineItems. Every MembershipChapter has zero or more OrderLineItems, every OrderLineItem belongs to exactly one MembershipChapter, namely the MembershipChapter that the foreign key refers to. There is a similar one to many relation between OrderLineItems and Products. Give me all (or some) MembershipChapters, each MembershipChapter with its zero or more OrderlineItems, and each OrderLineItem with its zero or more Products.

var result = dbContext.MemberShipChapters
    .Where(membershipChapter => ...)       // only if you don't want all MembershipChapters
    .GroupJoin(dbContext.OrderLineItems,

    membershipChapter => membershipChapter.Id, // from every membershipChapter get the primary key
    orderlineItem => orderLineItem.MembershipChapterId, // from every OrderlineItem get the foreign key

    // parameter resultSelector: from every MembershipChapter with its zero or more
    // OrderLineItems, make one new:
    (membershipChapter, orderLineItemsOfThisMembershipChapter) => new
    {
        // Select only the membershipChapter properties that you plan to use
        Id = membershipChapter.Id,
        Name = membershipChapter.Name,
        ...

        // The zero or more OrderLineItems of this membershipChapter
        OrderLineItems = orderLineItemsOfThisMembershipChapter
            .Select(orderLineItem => new
            {
                // Select only the OrderLineItems that you plan to use:
                Id = orderLineItem.Id,
                ...

                // not needed, you already know the value
                // MembershipChapterId = orderLineItem.MembershipChapterId,
            })
            .ToList(),
    });

This is fairly straightforward. However, if you want to GroupJoin three tables, then this looks horrible, although it is doable.

Another method that looks simpler:

    var result = dbContext.MemberShipChapters
    .Where(membershipChapter => ...)
    .Select((membershipChapter => new
    {
        Id = membershipChapter.Id,
        Name = membershipChapter.Name,
        ...

        OrderLineItems = dbContext.OrderLineItems

            // keep only the OrderLineItems with a foreign key referring to this MembershipChapter
            .Where(orderLineItem => orderLineItem.MemberShipChapterId == membershipChapter.Id)
            .Select(orderLineItem => new
            {
                Id = orderLineItem.Id,
                ...


                // do the same with the third table
                Products = dbContext.Products
                .Where(product => product.OrderLineItemId == orderLineItem.Id)
                .Select(product => new
                {
                     Id = product.Id,
                     Price = product.Price,
                     ...
                })
                .ToList(),
            })
            .ToList(),
        });