SQL "IN" as LINQ query using method syntax

Question

I have a set of related tables that I am trying to return a result set for and I just can't get the syntax quite right to get the results I want.

I am trying to return a list of countries (United States being the only one expected right now) with appropriate states. The "appropriate" requirement is that I only want to return the states under the countries that are represented by one of our clients....ie....if we have 3 clients, 2 in Texas and 1 in OK, I need the query to return "United States (with only Texas and OK...not the other 48 states where we don't have clients).

I can get the query to return only the United States, but it returns ALL states, not just the ones I am after. This is an example of the query that I "want" to run....NOTE: FirstAdminDivision table = states table.

select * from Country c
    inner join FirstAdminDivision f on f.CountryId = c.CountryId
where f.FirstAdminDivisionId IN 
(
    select f2.FirstAdminDivisionId from Company C
        inner join [Address] a on a.AddressId = c.AddressId
        inner join City cty on cty.CityId = a.CityId
        inner join FirstAdminDivision f2 on f2.FirstAdminDivisionId = cty.FirstAdminDivisionId
)

This is the code I currently have (which is as close as I have been able to get) that returns the US only with all states. The "ids" list contains only Texas and OK like I would expect, so I think they issue lies in the where in the main select.

IQueryable<int> innerQ = base.Context.Set<FirstAdminDivision>().Where(x => x.Cities.Any(y => y.Addresses.Any(z => z.Companies.Any()))).Select(x => x.FirstAdminDivisionId); 
            List<int> ids = innerQ.ToList();

            IQueryable<ICountryModel> q2 = base.Context.Set<Country>()
                .Include(x => x.FirstAdminDivisions)
                .Where(x => x.FirstAdminDivisions.Where(y => innerQ.Contains(y.FirstAdminDivisionId)).Any())
                .Select(x => new CountryModel
                {
                    Abbreviation = x.Abbreviation,
                    CountryId = x.CountryId,
                    Name = x.Name,
                    UrlDisplay = x.UrlDisplay,
                    FirstAdminDivisions = x.FirstAdminDivisions.Select(y => new FirstAdminDivisionModel
                    {
                        Abbreviation = y.Abbreviation,
                        Name = y.Name,
                        UrlDisplay = y.UrlDisplay
                    }).ToList()
                });

Any help pointing out what I am missing/doing wrong would be greatly appreciated.

Yes. The hierarchy is pretty simple....country/firstadmindivision/city. Each step in the hierarchy contains a foreign key to the table above. — user1011627
– user1011627, Commented Dec 25, 2014 at 16:31

Vasko Poposki · Accepted Answer · 2014-12-25 20:15:24Z

Well basically i would use your first query as the base query instead of all the countries and instead of returning a list of ints i would want it to return a list of FirstAdminDivision objects.

So in this case you would have two objects in that list OK and Texas. And in this case you also should have the country available since you say that FirstAdminDivision has the country as a property

Then from that list i would include country object so you can group those two state objects by country. And from that build your model using the key, country, and then the list of states.

Something like this:

    IQueryable<ICountryModel> countriesWithStates = base.Context.Set<FirstAdminDivision>()
        .Where(x => x.Cities.Any(y => y.Addresses.Any(z => z.Companies.Any())))
        .Include(x => x.Country)
        .GroupBy(x => x.Country, y=>y, (countryKey, states) => new { Country = countryKey, States = states.ToList() })
        .Select(x => new CountryModel
        {
            Abbreviation = x.Country.Abbreviation,
            CountryId = x.Country.CountryId,
            Name = x.Country.Name,
            UrlDisplay = x.Country.UrlDisplay,
            FirstAdminDivisions = x.States.Select(y => new FirstAdminDivisionModel
            {
                Abbreviation = y.Abbreviation,
                Name = y.Name,
                UrlDisplay = y.UrlDisplay
            }).ToList()
        });

Collectives™ on Stack Overflow

SQL "IN" as LINQ query using method syntax

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related