two conditions checking in where clause using linq 2 entites

Question

hi i have table called products with columns

                 product_id
                 prodcut_name
                 prodcut_price( values like 1200,2000,3000,100)
                 prodcut_desc
                 category_id(1,2,3,.....) 

anorther table categories with columns

              category_id(1,2,3.......)
              category_name(a,b,c....)

and entity name is dbcontext...

How can i get the products with category name "a" and product price is >500 and <10000

i am using linq to enitities...

would any pls help on this..one ....

Many thanks....

Solution 1

You are looking for logic operators, and these are the ones you are going to use in c#:

• && logical AND
• || logical OR
• ! logical NOT
• ^ logical (bitwise) XOR
• COND ? TRUE-STATEMENT : FALSE-STATEMENT ternary operator

So your composite condition in pseudo code going to look like:

product_price > 500 AND product_price < 10000

Now, if you have no foreign key in DB, when you've created your context it only going to have DBSets, and they are not going to have navigation properties. So your only option is to use Cubicle.Jockey's answer.

If you have foreign keys in DB, you will have navigation properties on your entity objects and you will be able to do the following:

var query = from p in dbcontext.products
where p.category.name == 'a' 
   && p.product_price > 500 
   && p.product_price < 10000
select p;

Alternatively you can use LINQ extension methods directly:

  var query = dbcontext.Products.Where(p => p.category.name == 'a' 
                                         && p.product_price > 500 
                                         && p.product_price < 10000);

If you need a list or array and want close dbcontext you are calling either ToList or ToArray on query:

var products = query.ToList();

There is alternative way of doing it with Entity Framework (EF) - build-in EntitySQL query language. You are going to have similar expression with it:

var query = ProductsQuery.Where("it.Category.Name == 'a' AND it.Product_Price BETWEEN 500 AND 10000");

where ProductsQuery is ObjectQuery<Product>.

Solution 2

from p in dbcontext.products
join c in dbcontext.category on p.category_id equals c.category_id
where p.product_price > 500 && p.product_price < 10000 && c.Name == "a"
select p

This will return you an IQueryable with the filtered results.

Based on Vittore's answer below, if there is a foreign key constraints this would be the more appropriate version:

from p in dbcontext.products
where p.category.name =='a' && p.product_price > 500 && p.product_price < 10000
select p;

Solution 3

join the product and categories tables on category_id and then use a where to filter out product_price.