CODEWITHSUNDEEP
c#sqllinqlambda
sensei
sensei

Reputation: 7562

Linq lambda expression many to many table select

I have three tables, which two of them are in many to many relationship.

Picture:

enter image description here

This is the data in middle mm table:

enter image description here

Edit: Got until here, I get proper 4 rows back, but they are all the same result(I know I need 4 rows back, but there are different results)

    return this._mediaBugEntityDB.LotteryOffers
        .Find(lotteryOfferId).LotteryDrawDates
        .Join(this._mediaBugEntityDB.Lotteries, ldd => ldd.LotteryId, lot => lot.Id, (ldd, lot) =>
            new Lottery
            {
                Name = lot.Name,
                CreatedBy = lot.CreatedBy,
                ModifiedOn = lot.ModifiedOn
            }).AsQueryable();

My question is, how can I retrieve all the Lotteries via many to many table WHERE I have LotteryOfferId given only?

What I want to achieve is to get data from lottery table by LotteryDrawDateId.

First I use LotteryOfferId to get DrawDates from middle table, and by middle table I get drawDateIds to use them in LotteryDrawDate table. From that table I need to retreive Lottey table by LotteryId in LotteryDrawDate table.

I gain this by normal SQL(LotteryOffersLotteryDrawDates is middle table in DB, not seen in model):

select Name, Lotteries.CreatedBy, Lotteries.ModifiedOn, count(Lotteries.Id) as TotalDrawDates from Lotteries join LotteryDrawDates on Lotteries.Id = LotteryDrawDates.LotteryId join LotteryOffersLotteryDrawDates on LotteryDrawDates.Id = LotteryOffersLotteryDrawDates.LotteryDrawDate_Id where LotteryOffersLotteryDrawDates.LotteryOffer_Id = 19 group by Name, Lotteries.CreatedBy, Lotteries.ModifiedOn

But Linq is different story :P

I would like to do this with lambda expressions. Thanks

Upvotes: 4

Views: 8290

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109099

You can do this:

var query = from lo in this._mediaBugEntityDB.LotteryOffers
            where lo.lotteryOfferId == lotteryOfferId
            from ld in lo.LotteryDrawDates
            group ld by ld.Lottery into grp
            select grp.Key;

I do this in query syntax, because (in my opinion) it is easier to see what happens. The main point is the grouping by Lottery, because you get a number of LotteryDrawDates any of which can have the same Lottery.

If you want to display the counts of LotteryDrawDates per Lottery it's better to take a different approach:

from lot in this._mediaBugEntityDB.Lotteries.Include(x => x.LotteryDrawDates)
where lot.LotteryDrawDates
         .Any(ld => ld.LotteryDrawDates
                      .Any(lo => lo.lotteryOfferId == lotteryOfferId))
select lot

Now you get Lottery objects with their LotteryDrawDates collections loaded, so afterwards you can access lottery.LotteryDrawDates.Count() without lazy loading exceptions.

Upvotes: 1

Moho
Moho

Reputation: 16498

db.LotteryOffer.Where(lo => lo.Id == <lotteryOfferId>)
    .SelectMany(lo => lo.LotteryDrawDates)
    .Select( ldd => ldd.Lottery )
    .GroupBy( l => new { l.Name, l.CreatedBy, l.ModifiedOn } )
    .Select( g => new
    {
        g.Key.Name,
        g.Key.CreatedBy,
        g.Key.ModifiedOn,
        TotalDrawDates = g.Count()
    } );

Upvotes: 3

Related Questions