Using v2.6, built on Oct-6-2008, Adapter, .NET 3.5, Linq, 2 class scenario, SQL Server Specific.

I need some direction regarding which code I could write.

I need to execute this query:

SELECT c.CompanyId, c.[Company_Name], o.[OfficeName], o.[Address_Line_1], o.[Address_Line_2], o.[City], o.[PostalCode], sc.[Abbreviation], sc.[FullName], cc.[Abbreviation], cc.[FullName] FROM [Companies] c 

INNER JOIN [Company_Membership_Link] cm ON cm.[CompanyID] = c.[CompanyID] 
INNER JOIN [Offices] o ON o.[CompanyID] = c.[CompanyID]
LEFT JOIN [State_Codes] sc ON o.[StateCodeID] = sc.[StateCodeID]
INNER JOIN [Country_Codes] cc ON cc.[CountryCodeID] = sc.[CountryCodeID]

WHERE cm.[ProgramID] = 2

GROUP BY c.[CompanyId], c.[Company_Name], o.[OfficeName], o.[Address_Line_1], o.[Address_Line_2], o.[City], o.[PostalCode], sc.[Abbreviation], sc.[FullName], cc.[Abbreviation], cc.[FullName]

ORDER BY c.[Company_Name]

I am pretty sure that the following options are available -> 1. Create Typed List in the designer (all the proper relations are in place to do this) 2. Create a Typed View in the designer from a view in the DB 3. Create a dynamic list in C# 4. Create & code gen access to a retreival stored procedure 5. Do option 1,3, or 4 with a projection to a custom class

Am I missing any options?

Is there a way to linq this?

Unfortunately, I inherited the DB, so it is kind of fugly (field names, table names etc). All 5 tables have a bunch of crap fields that I dont need in my result set.

Note -> this query will most likely evolve into "WHERE cm.ProgramId AND o.PostalCode IN (_ArrayOfPostalCodes_)

I am leaning toward a dynamic list in C# possibly with a projection.

Any thoughts on the proper implementation?

All the options you listed are indeed valid. I would have used a dynamicList because it avoids changesto the database and in LLBLGen Designer.

Also you may linqfy this, by adding the appropriate Joins and GroupBy, and then projecting by using select new {}, to select the fields you want to return from the different involved entities.

Thanks for the clarification. It was really helpful.

I am struggling with the grouping. Can you point me in the right direction? I am a newb when it comes to Linq.

using (DataAccessAdapter adapter = GetAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);
    var results = (from company in metaData.Companies
       join office in metaData.Offices on company.CompanyId equals office.CompanyId
       join states in metaData.StateCodes on office.StateCodeId equals states.StateCodeId into tmpStateCode
       join countries in metaData.CountryCodes on office.CountryCodeId equals countries.CountryCodeId into tmpCountryCode
       join membership in metaData.CompanyMembershipLink on company.CompanyId equals membership.CompanyId
       from countryCode in tmpCountryCode.DefaultIfEmpty()
       from stateCode in tmpStateCode.DefaultIfEmpty()
       where membership.ProgramId == 2 && postalCodes.Contains(office.PostalCode)
       orderby company.CompanyName
       //group company by new {
       //   company.CompanyId,
       //   company.CompanyName,
       //   company.Offices.,
       //   office.AddressLine1,
       //   office.AddressLine2,
       //   office.City,
       //   office.PostalCode,
       //   CountryCodeAbbr = countryCode.Abbreviation,
       //   CountryCodeFullName = countryCode.FullName,
       //   StateCodeAbbr = stateCode.Abbreviation,
       //   StateCodeFullName = stateCode.FullName
       //} into gOffice

       select new {
           company.CompanyId,
           company.CompanyName,
           office.OfficeName,
           office.AddressLine1,
           office.AddressLine2,
           office.City,
           office.PostalCode,
           CountryCode = countryCode.Abbreviation,
           CountryName = countryCode.FullName,
           StateCode = stateCode.Abbreviation,
           StateName = stateCode.FullName
       });



    foreach (var item in results) {
        Console.WriteLine("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10}", item.CompanyId, item.CompanyName, item.OfficeName,
        item.AddressLine1, item.AddressLine2, item.City, item.PostalCode, item.CountryCode, item.CountryName,
        item.StateCode, item.StateName);
    }

Note that the current group statement is commented out. I cant seem to figure that part out.

Here is the output from the linq statement above. I just need to group it and I am all set.

6216,Accurate Printers,,6081 Oakbrook Pkwy,,Norcross,30093,US,United States,GA,Georgia
572,Acuity Digital Imaging,,20 E Pearce St,,Richmond Hill,L4B 1B7,CA,Canada,ON,Ontario
538,Wilcor Graphics,,6364 Warren Dr,,Norcross,30093,US,United States,GA,Georgia
538,Wilcor Graphics,,6364 Warren Dr,,Norcross,30093,US,United States,GA,Georgia

Got it worked out.... (you may want to move this thread to the Linq section)

using (DataAccessAdapter adapter = GetAdapter()) {
    LinqMetaData metaData = new LinqMetaData(adapter);
    var results = (from company in metaData.Companies
                   // inner joins
                   join office in metaData.Offices on company.CompanyId equals office.CompanyId
                   join states in metaData.StateCodes on office.StateCodeId equals states.StateCodeId into tmpStateCode
                   join countries in metaData.CountryCodes on office.CountryCodeId equals countries.CountryCodeId into tmpCountryCode
                   join membership in metaData.CompanyMembershipLink on company.CompanyId equals membership.CompanyId
                
                   // left joins
                   from countryCode in tmpCountryCode.DefaultIfEmpty()
                   from stateCode in tmpStateCode.DefaultIfEmpty()
                
                   // predicate
                   where membership.ProgramId == 2 && postalCodes.Contains(office.PostalCode)
                
                   // group into anonymous type
                   group company by new { company.CompanyId, company.CompanyName, office.OfficeName, office.AddressLine1,
                       office.AddressLine2, office.City, office.PostalCode, CountryCode = countryCode.Abbreviation,
                       CountryName = countryCode.FullName, StateCode = stateCode.Abbreviation, StateName = stateCode.FullName
                   } into g

                   // order
                   orderby g.Key.CompanyName

                   // project results into anonymous type
                   select new { g.Key.CompanyId, g.Key.CompanyName, g.Key.OfficeName, g.Key.AddressLine1, g.Key.AddressLine2,
                       g.Key.City, g.Key.PostalCode, CountryCode = g.Key.CountryCode, CountryName = g.Key.CountryName,
                       StateCode = g.Key.StateCode,StateName = g.Key.StateName}
                   );


    foreach (var item in results) {
        Console.WriteLine("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10}", item.CompanyId, item.CompanyName, item.OfficeName,
        item.AddressLine1, item.AddressLine2, item.City,
        item.PostalCode, item.CountryCode, item.CountryName,
        item.StateCode, item.StateName);
    }