CODEWITHSUNDEEP
c#linq
ConfusedSleepyDeveloper
ConfusedSleepyDeveloper

Reputation: 863

Writing a SQL query with LINQ

I have this so far:

var query = (from g in this.Context.Groups
    where g.ID == groupID &&
          g.TaxId == groupTaxId
    select g.Group_K);

Then the SQL query I want to add is this:

select zipCode 
from ADDRESSES 
where ADDRESS_K in 
   (select ADDRESS_K 
    from GROUPADDRESS 
    where GROUP_K = "is in Group_K found above "
       and address_type = "address type variable I pass into this method"
       and ACTIVE = 1)

Notice that GROUPADDRESS is the bridge table between GROUPS and Addresses table that has Group_K and Address_K I can't figure it out how to add a new LINQ query or update the one I have to account for the new SQL I am trying to add. Thanks for help.

Groups Table:  Group_K, ID, TaxId
Addresses Table: Address_K, Zip
GroupAddress Table:  Group_K, Address_K, Address_Type

Upvotes: 1

Views: 91

Answers (2)

kidshaw
kidshaw

Reputation: 3451

Assuming you have a GroupAddress in your context:

var query = (from g in this.Context.Groups
join ga in this.Context.GroupAddress on g.AddressK equals ga.AddressK
where g.ID == groupID &&
      g.TaxId == groupTaxId
select g.Group_K);

Edit:

Right so GroupAddress sits between Group and Address. Assuming you've set up relationships in your database, you will have navigation properties you can test against:

where g.Address == ''

EF drops link tables so your navigation is simpler. If not available in EF, add another join to the above.

var query = (from g in this.Context.Groups
join ga in this.Context.GroupAddress on g.Group_K equals ga.Group_K
join a in this.Context.GroupAddress on g.Address_K equals ga.Address_K
where g.ID == groupID &&
      g.TaxId == groupTaxId
select g.Group_K);

Upvotes: 1

Arion
Arion

Reputation: 31249

Maybe something like this:

var groupID=1;
var groupTaxId=1;
var result=
(
    from a in this.Context.ADDRESSES
    where
    (
        from ga in this.Context.GROUPADDRESS
        join g in this.Context.Groups
            on g.GROUP_K equals ga.GROUP_K
        where ga.ID == groupID
        where g.TaxId== groupTaxId
        where ga.ACTIVE == 1
        select ga.ADDRESS_K
    ).Contains(a.ADDRESS_K)
    select new
    {
        a.zipCode
    }
).ToList();

Upvotes: 1

Related Questions