How to find specific item with Linq based on multiple conditions?

user_10339036 0
Viewed 429

I have pretty simple query:

//user from UserManager from default AccountController from .net Identity
var user = await UserManager.FindByIdAsync(User.Identity.GetUserId());

var product = await Task.Run(() 
    => db.WatchedProducts.Where(u => u.ApplicationUserId == user.Id && u.ProductId == id));

What i want to do is to find specific product in WatchedProducts list. Model of it looks like this:

public class WatchedProduct
{
    [Key]
    public int Id { get; set; }

    [ForeignKey("ApplicationUser")]
    public string ApplicationUserId { get; set; }
    public virtual ApplicationUser ApplicationUser { get; set; }

    [ForeignKey("Product")]
    public int ProductId { get; set; }
    public virtual Product Product { get; set; }
}

ApplicationUser have list of WatchedProducts.

My question is, why istead of getting WatchedProduct product i get an IQueryable<WatchedProduct> product?

asp.net-identity asp.net-mvc linq c#
3 Answers

It happens because you are using method Where(). Where() method filters your data based on your lambda-expression => u.ApplicationUserId == user.Id && u.ProductId == id and returns IQueryable<TSource> or IEnumerable<TSource>(See great explanation in answer by Reza Aghaei).

If you want to get WaterProduct product then just get it through FirstOrDefault() method:

var product = await Task.Run(() 
    => db.WatchedProducts.Where(u => u.ApplicationUserId == user.Id && u.ProductId == id)
       .FirstOrDefault());

You did not get any data as you did not materialized your query. It is called deferred execution. Deferred execution means that your linq code will not be executed in database until you need necessary data. So to materialize data or execute your query in database you should call methods like:

foreach, toList(), First(), FirstOrDefault(), Single(), SingleOrDefault(), etc...
user_16462400

It's because Where extension method returns IEnumerable<TSource>. In case of db set, it returns IQueryable<TSource> which is IEnumerable<TSource>.

In your case, since you are using async/await pattern, you can use FirstOrDefaultAsync to get a single item as a result:

var p = await db.WatchedProducts.FirstOrDefaultAsync(u => u.ApplicationUserId == user.Id &&
                                                          u.ProductId == id)
user_31108340

You should use SingleOrDefault

From what i can tell your result should be unique since you are using product id which would be unique

var product = await Task.Run(() 
    => db.SingleOrDefault(u => u.ApplicationUserId == user.Id && u.ProductId == id));

This will return one item or null if none was found. Caution it will throw an exception if more then one item is found, which it shouldn't since product id would most likely be unique and if it finds more the one you would know that you messed up in your database that you have multiple products with the same id

If its acceptable that that query can have more then one record as a result then use FirstOrDefault instead of SingleOrDefault but then the logic wouldn't be quite correct since it doesn't make sense to have a query that needs to return one or none, to return the first on the list.

user_51954900
Related