CODEWITHSUNDEEP
c#linqentity-framework-core
Bugbeeb
Bugbeeb

Reputation: 2151

LINQ greater/less than not working in query

var query = db.Products.Where(p => p.Cost == 10M); works perfectly fine and returns the correct list of results. var query = db.Products.Where(p => p.Cost > 10M); throws an exception:

The LINQ expression 'DbSet<Product>.Where(p => p.Cost > (Nullable<decimal>)10)' could not be translated.

Obviously the script being generated by C# can't be executed in SQL but why not? That is a valid SQL query

public class Product
    {
        public int ProductID {get;set;}
        [Required]
        [StringLength(40)]
        public string ProductName {get;set;}
        [Column("UnitPrice", TypeName="money")]
        public decimal? Cost {get;set;}
    }

    public class Northwind: DbContext
    {
        public DbSet<Category> Categories {get;set;}
        public DbSet<Product> Products {get;set;}
    }

EFCore 3.1.2, .NET Core 3.1.1

Upvotes: 0

Views: 1520

Answers (3)

Deniz
Deniz

Reputation: 366

var query = db.Products.Where(p => p.Cost.HasValue && p.Cost.Value > 10M);

A readable form of this query can be written as above.

Upvotes: 0

Arthur Rey
Arthur Rey

Reputation: 3058

It looks like p.Cost is a decimal?. In that case you can use the null coalescing operator.

var query = db.Products.Where(p => (p.Cost ?? 0) > 10M);

Upvotes: 4

Michael Ceber
Michael Ceber

Reputation: 2452

This is because Cost is a nullable decimale, i.e decimal?

Equals does not throw an error because it can check if null == 10M, however, to do a greater than it would have to go p.Cost.Value behind the scenes, but it does not know how to do that.

So two choices Chane p.Cost to a 'decimal' rather than a nullable type OR Assuming p.Cost is never null which is probably the case simply change the linq to

p => p.Cost.Value > 10M

Upvotes: 3

Related Questions