Help with linq join

Question

Hi there,

I have the following expression in linq (its a join) and i am selecting into "J" because i need to use J later (currently i just selecting J but once i have this fixed i plan on use J within another subquery after)

But it won't let me supply a where using the "V" side hence v.IdOFfice is invalid. I have tried swapping around the joins and that what happens i can't use the "GVT"..

WIth specifying the where it works perfect but i need to specify 2 wheres that are present in the 2 tables ... hence IdOffice and IdTariff are in there own tables .. they are not both ....

(from gvt in Tariffs
join v in Items
on gvt.IdGroupItem equals v.IdGroupItem 
into j
where v.IdOffice == 1 && gvt.IdTariff == 111
select j).Take(50)

Probably something silly, it appears the table specified after the join i am not able to use in the where?

Any ideas?

Thanks

This is basically what i am trying to achieve

from gvt in Tariffs
  join v in Items
  on gvt.IdGroupItem equals v.IdGroupItem 
  into j
  where v.IdOffice == 1 && gvt.IdTariff == 111
  select new
  {
      id = v.IdItem
      Tariff = from j
      {
    test = j.TariffDesc,
        test1 = j.TariffPrice
      }

basicaly i end up with 1 record with Id and a field which as many tariffs inside - if this makes sense?

}

Query working great,

it would be nice to be able to use an extension method (c#) like so ... is this possible so i can dynamically set tariff ... so for example i do the query and i have an extension method (which i already use on simple queries) like so

    public static IQueryable<Models.ItemTariffCol> WithTariffId(this IQueryable<Models.ItemTariffCol> qry, int tariffId)
    {
        return from t in qry
               where t.IdTarifa == tariffId
               select t;
    }

this makes it very extensible ? If its a normal where i can do this but the query isn't in the where

Thank you.

Answer 1

You're doing a group join here, since you're using into. This means that for every gvt, you have not one Item, but possibly several (or none). The list of all items is stored in j, as an IEnumerable<Item>. If you want to select all tariffs for which there's at least one item with IdOffice == 1, then you can do it like this:

from gvt in Tariffs
join v in Items
     on gvt.IdGroupItem equals v.IdGroupItem 
     into j
where gvt.IdTariff == 111 && j.Any(v => v.IdOffice == 1)
...

After the answer edit, it seems that you've started from the wrong direction as well - so far as I can see, you want a list of tariffs for every item, not the list of items for every tariff. For that, you need to reverse your join:

from item in Items
join tariff in Tariffs
     on item.IdGroupItem equals tariff.IdGroupItem 
     into tariffs
where item.IdOffice == 1
select new
{
    Id = item.IdItem,
    Tariffs = from tariff in tariffs
              where tariff.IdTariff == 111
              select new { tariff.TariffDesc, tariff.TariffPrice }
}

Or you could filter tariffs right in the join:

from item in Items
join tariff in (from t in Tariffs where t.IdTariff == 111 select t)
     on item.IdGroupItem equals tariff.IdGroupItem 
     into tariffs
where item.IdOffice == 1
select new
{
    Id = item.IdItem,
    Tariffs = from tariff in tariffs
              select new { tariff.TariffDesc, tariff.TariffPrice }
}