Tom,

>I am using EF with a SQL back end. MM4.0
>I would like to accomplish a complex query in a single query in my BO but haven’t yet figured out how (not using stored procedures). Maybe someone can give me a hand. Here’s what I’ve got:
>
>I am working in the UnitType.Partial.cs BO
>I want to pass in 2 parameters: CustomerPK and CustomerGroupPK
>All of the primary-foreign key mappings are setup in the EDMX
>
>a) I have a CustomerUnitTypeGroups table, the 3 important fields in this table are:
>CustomerUnitTypeGroupPK
>CustomerFK
>CustomerGroupFK
>
> Need to get all CustomerUnitTypeGroups records that have a matching CustomerFK or CustomerGroupFK
>
>b) Next I have a CustomerUnitTypeGroupMembers table, the 2 important fields in this table are:
>CustomerUnitTypeGroupFK
>UnitTypeGroupFK
>
>Need to get all CustomerUnitTypeGroupMembers records that have a matching CustomerUnitTypeGroupFK from a) above
>
>c) Next I have a UnitTypeGroupMembers table, the 2 important fields in this table are:
>UnitTypeGroupFK
>UnitTypeFK
>
>Need to get all UnitTypeGroupMembers records that have a matching UnitTypeGroupFK from b) above
>
>d) finally I have a UnitTypes table, the important field in this table is:
>UnitTypePK
>
>Need to get an entity list of all UnitType records that have a matching UnitTypePK from c) above
>
>This final entity list of unit types is what I want to return from the procedure.
>
>I figured out how to do it with nested query/foreach loops, but I would like something more efficient (elegant).

This is a more generic .NET question, which is best posted in one of the other categories to give other .NET developers a stab at answering the question for you. That said, I asked Josh to take a look at your scenario and he came up with this:

IQueryable<UnitTypeEntity> query = (from ut in this.ObjectContext.UnitTypeEntities
                         join utgm in this.ObjectContext.UnitTypeGroupMemberEntities
                         on ut equals utgm.UnitType
                         join cutgm in this.ObjectContext.CustomerUnitTypeGroupMemberEntities
                         on utgm.UnitTypeGroup equals cutgm.UnitTypeGroup
                         join cutg in this.ObjectContext.CustomerUnitTypeGroupEntities
                         on cutgm.CustomerUnitTypeGroup equals cutg
                         where cutg.CustomerFK == customerPK || cutg.CustomerGroupFK == customerGroupPK
                         select ut).Distinct();
           
            return this.GetEntityList(query);

Best Regards,