CODEWITHSUNDEEP
c#asp.net.net
Umair Abid
Umair Abid

Reputation: 1463

How to add new where condition in IQueryable when selecting a view model?

I am fairly new to C# and .Net, so apologies if something doesn't make sense, I will try my best to explain my problem.

I have two methods which are basically going to use the similar query but with slight differences. So instead of repeating the query in both methods I created a third private method which will return the common part of the query and then the functions can add more clauses in query as they require.

Here is a generic function which returns the IQueryable object with common part of the query

private IQueryable<OfferViewModel> GetOffersQueryForSeller(int sellerId)
{
    return Db.Offers
        .Where(o => o.Sku.SellerId == sellerId && o.IsActive && !o.IsDiscontinued)
        .Select(o => new OfferViewModel
        {
            Id = o.Id,
            Name = o.Sku.Name,
            ImageUrl = o.Sku.ImageUrl ?? o.Sku.Upcq.Upc.ImageUrl,
            QuantityName = o.Sku.QuantityName
        });
}

Following are the two method which are reusing the IQueryable object

public async Task<List<OfferViewModel>> GetSellerOffers(int sellerId)
{
    var query = GetOffersQueryForSeller(sellerId);
    return await query.ToListAsync();
}

public async Task<List<OfferViewModel>> GetDowngradableSellerOffers(int sellerId)
{
    var query = GetOffersQueryForSeller(sellerId);
    return await query
        .Where(o => o.Sku.Id == monthlySkuId)
        .ToListAsync();
}

Now GetSellerOffers works just fine but GetDowngradableSellerOffers throws a run time error with message The specified type member 'Sku' is not supported in LINQ to Entities.. I asked around and one of the guys told me that I cannot add additional where after adding a select which uses a ViewModel because then my records will be mapped to ViewModel and LINQ will attempt to look up props of ViewModel instead of database columns.

Now I have two questions,

  1. In the docs I read Entity Framework will only run query when I try to fetch the results with methods like ToList and if I haven't done that why it wouldn't allow me to apply conditions on database fields/

  2. How can I reuse the common query in my scenario?

Upvotes: 4

Views: 2593

Answers (1)

Freggar
Freggar

Reputation: 1066

How about the following code:
(The type Offer should be replaced by the type of the Elements that Db.Offers holds) 

private IQueryable<OfferViewModel> GetOffersQueryForSeller(int sellerId, Func<Offer,bool> whereExtension)
{
    return Db.Offers
        .Where(o => ... && whereExtension.Invoke(o))
        .Select(o => new OfferViewModel { ... });
}

private IQueryable<OfferViewModel> GetOffersQueryForSeller(int sellerId)
{
    return GetOffersQueryForSeller(sellerId, (o) => true);
}

And then call it in GetDowngradableSellerOffers like this:

public async Task<List<OfferViewModel>> GetDowngradableSellerOffers(int sellerId)
{
    var query = GetOffersQueryForSeller(sellerId, (o) => o.Sku.Id == monthlySkuId);
    return await query.ToListAsync();
}

Upvotes: 1

Related Questions