CODEWITHSUNDEEP
c#linqjoinlinq-to-entities
user3653673
user3653673

Reputation: 49

LINQ Conditionally Add Join

I have a LINQ query where I'm trying to return data from 2 tables, but the tables that I join are conditional.

This is what I'd like to do:

if (teamType == "A"){
    var query = from foo in context.People
                join foo2 in context.PeopleExtendedInfoA
                select foo;
}
else {
    var query = from foo in context.People
                join foo2 in context.PeopleExtendedInfoB
                select foo;
}

Then later on I'm filtering the query down even further. I obviously can't set it up this way because I won't be able to access "query" outside the if block, but it shows what I'm trying to do. This is an example of what I'm trying to do later on with the query:

if (state != null)
{
     query = query.Where(p => p.State == state);
}

if (query != null) {
   var queryFinal = from foo in query
         select new PeopleGrid()
         {
              Name = foo.Name,
              Address = foo.Address,
              Hobby = foo2.Hobby
         }
}

What I'm trying to return is all the data from table foo and then one field from the joined table, but depending on the logic, the joined table will differ. Both PeopleExtendedInfoA and PeopleExtendedInfoB both have the columb 'Hobby', but I have no way to access 'Hobby' from the joined table and that's the only field I need from the joined table.

How would I go about doing this?

Upvotes: 3

Views: 3170

Answers (4)

user3653673
user3653673

Reputation: 49

I figured it out. Thanks everyone for all the replies, it got my brain working again and gave me some new ideas (even though I didn't directly take any of them) I realize I needed to do the join at the very end instead of the beginning that way I don't have to deal with filtering on different types. This is what I did:

var query = from foo in context.People
            select foo;

if (state != null)
{
    query = query.Where(p => p.State == state);
}

if (query != null) {
   if (teamType == "A")
   {
      var queryFinal = from foo in query
                       join foo2 in context.PeopleExtendedInfoA
      select new PeopleGrid()
      {
          Name = foo.Name,
          Address = foo.Address,
          Hobby = foo2.Hobby
      }
   }
   else
   {
      var queryFinal = from foo in query
                       join foo2 in context.PeopleExtendedInfoB
      select new PeopleGrid()
      {
          Name = foo.Name,
          Address = foo.Address,
          Hobby = foo2.Hobby
      }
   }
}

Upvotes: 0

Ocelot20
Ocelot20

Reputation: 10800

You can query into an intermediate type that holds the relevant fields, or if the query is simple enough you can use anonymous types as seen below. The important part is that the Join calls both have the same return types ({ p.Name, a.Hobby } and { p.Name, b.Hobby } will both be the same anon type).

var baseQuery = context.People;

var joined = type == "A" ?
    baseQuery.Join(PeopleExtendedInfoA,
        p => p.Id,
        a => a.PeopleId,
        (p, a) => new { p, a.Hobby }) :
    baseQuery.Join(PeopleExtendedInfoB,
        p => p.Id,
        b => b.PeopleId,
        (p, b) => new { p, b.Hobby });

var result = joined.Select(x => new
    {
        x.p.Name,
        x.p.Address,
        // etc.
        x.Hobby
    };

Upvotes: 0

Felipe Oriani
Felipe Oriani

Reputation: 38608

Does PeopleExtendedInfoA and PeopleExtendedInfoB inherits from the same base class? You could create a IQueryable<BaseClass> and let the linq provider solve it for you when you add the join. For sample:

IQueryable<BasePeople> basePeople;
if (teamType == "A")
   basePeople = context.PeopleExtendedInfoA;
else
   basePeople = context.PeopleExtendedInfoB;

var query = from foo in context.People
            join foo2 in basePeople on foo.Id equals foo2.PeopleId
            select new PeopleGrid()
            {
              Name = foo.Name,
              Address = foo.Address,
              Hobby = foo2.Hobby
            };

Upvotes: 2

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

try like this:

 var queryFinal = from foo in query
                  where foo.State == state !=null ? state : foo.State
         select new PeopleGrid()
         {
              Name = foo.Name,
              Address = foo.Address,
              Hobby = foo2.Hobby
         }

Upvotes: 0

Related Questions