Linq-to-SQL query a list

Question

This is most likely very simple it's just that i can't get it to work correctly.

I have 2 c# classes:

Market

public string MarketId { get; set; }

public string Description { get; set; }

public virtual List<Dealer> Dealers { get; set; }

Dealer

public string DealerId { get; set; }

public string City { get; set; }

public Market Market { get; set; }

What i want to do is to get a specific market or markets (marketid) and ONLY get the dealer(s) that match the (dealerid). Can be multiple matches.

I tried with this:

dbMarkets = db.Markets.Where(x => x.MarketId.Equals(marketId) && x.Dealers.Select(c => c.DealerId).Contains(dealerId)).ToList();

That one however returns all dealers that belong to a market and not just the ones that match dealerid.

Answer 1

Since Market.Dealers property is just a navigation, you need to load dealers separately. But in this case, every Dealer also has Market as a navigation property, you can query to Dealer including Market first and then grouping by Market after. like this:

using (var db = new MyContext())
{
    // this is important since Market.Dealers is a navigation property,
    // without this it would load all dealers in the market.
    db.Configuration.LazyLoadingEnabled = false;

    // load all dealers and its market(using Include)
    // by specific marketId and dealerId.
    var dealers = db.Dealers
        .Include(o => o.Market)
        .Where(o => o.DealerId == dealerId && o.Market.Id == marketId)
        .ToList();

    // this will be executed in the client-side, 
    // so you don't need to worry about round-trip overhead.
    var markets = dealers
        .GroupBy(o => o.Market)
        .Select(o => o.Key)
        .ToList();
    Console.WriteLine("Check if all dealers also have market: " +
                  markets.SelectMany(o => o.Dealers).All(o => o.Market != null));
    Console.WriteLine("All dealers in the markets: " +
                  markets.SelectMany(o => o.Dealers).Count());
}

If you don't like to set configuration or write extra code(grouping in this case), you can project Market information and Dealers as an anonymous object. But this approach can't keep the reference to Market property on the each Dealers.

using (var db = new MyContext())
{
    var anonymousMarkets = db.Markets
        .Where(o => o.MarketId == marketId)
        .Select(o => new        // project as an anonymous object
        {
            o.MarketId,
            o.Description,
            Dealers = o.Dealers.Where(d => d.DealerId == dealerId)
        })
        .ToList();
    Console.WriteLine("Check if all dealers don't have market: " +
        anonymousMarkets.SelectMany(o => o.Dealers).All(o => o.Market == null));
    Console.WriteLine("All dealers in the markets: " + 
        anonymousMarkets.SelectMany(o => o.Dealers).Count());
}

Finally, if you want to keep Market navigation property on each Dealers, you can aggregate Market and Dealers together.

using (var db = new MyContext())
{
    var marketAggregates = db.Markets.Where(o => o.MarketId == marketId)
        .Select(o => new    // aggregate Market and Dealers as an anonymous object.
        {
            Market = o,
            Dealers = o.Dealers.Where(d => d.DealerId == dealerId)
        })
        .ToList();
    Console.WriteLine("Check if all dealers also have market: " +
          marketAggregates.SelectMany(o => o.Dealers).All(o => o.Market != null));
    Console.WriteLine("All dealers in the markets: " + 
          marketAggregates.SelectMany(o => o.Dealers).Count());
}

Well, all approaches have different pros and cons, but I prefer the last one usually.