CODEWITHSUNDEEP
c#linqlinq-to-sql
majid
majid

Reputation: 79

Get list of child records

I have a database that looks like this:

tbl_Seminar
   ID
   isActive


tbl_SeminarFees
   ID
   seminar_id -- foreign key
   fee_text

I want to get all seminars that are active (isActive ==1) and a list of the fees associated with that seminar. Each Seminar may have n records in tbl_SeminarFees that are its fees. I am able to return a linq structure that returns me a list of objects that look like this {seminar, SeminarFee} but I wanted to create a nested structure that looks like this:

{seminar, List<SeminarFee>}

What should my linq query look like?

here is my linq currently:

var results = from s in context.Seminar
              join p in context.SeminarFees on
              s.ID equals p.SeminarID

              where s.IsActive == 1
              select new 
              {
                  Seminar = s,
                  Fees = p

              };

How do I change this to get a list of these: {seminar, List<SeminarFee>}

Thanks

UPDATE

@lazyberezovsky gave me a good idea to use a group join and into another variable. But then how do I loop through the result set. Here is what I have now:

foreach (var seminarAndItsFeesObject in results)
            {
                //do something with the seminar object 
                //do something with the list of fees
            }

This however gives me the following error:

    Argument type 'SeminarFees' does not match the 
corresponding member type 
'System.Collections.Generic.IEnumerable`1[SeminarFees]'

What am I doing wrong?

Thanks

Upvotes: 2

Views: 307

Answers (2)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236218

You can use group join which groups inner sequence items based on keys equality (a.k.a. join..into) to get all fees related to seminar:

var results = from s in context.Seminar
              join f in context.SeminarFees on
                   s.ID equals f.SeminarID into fees // here 
              where s.IsActive == 1
              select new 
              {
                  Seminar = s,
                  Fees = fees
              };

You can't call ToList() on server side. But you can map results on client later.

BTW You can define navigation property Fees on Seminar object:

public virtual ICollection<SeminarFee> Fees { get; set; }

In this case you will be able load seminars with fees:

var results = context.Seminar.Include(s => s.Fees) // eager loading
                     .Where(s => s.IsActive == 1);

Upvotes: 6

Ondrej Svejdar
Ondrej Svejdar

Reputation: 22054

  var results = from s in context.Seminar
                join p in context.SeminarFees on s.ID equals p.SeminarID
                where s.IsActive == 1
                group p by s into grouped
                select new {
                  Seminar = grouped.Key,
                  Fees = grouped.ToList()
                };

Upvotes: 1

Related Questions