Get value from Database to Linq where clause

Question

I have a table called FILTRE_LINKLER and column's names are

ID
SEF_URL
CONDITIONS

For example,

ID=1
SEF_URL="test/"
CONDITIONS="STOCK>50"`

I want to get CONDITIONS part to linq where clause.

var product = (from d in db.PRODUCTS
                       where *CONDITIONS from DB*
                       select new ProductModel
                       {
                           Description= d.DESCRIPTION,
                           Brand= d.BRANDS.BRAND,
                           SefUrl = sef_url,
                           Name= d.NAME,

                       });

I try to that:

var query = db.FILTRE_LINKLER.Select(x => x.CONDITIONS);
 var product = (from d in db.PRODUCTS
                       where query
                       select new ProductModel
                       {
                           Description= d.DESCRIPTION,
                           Brand= d.BRANDS.BRAND,
                           SefUrl = sef_url,
                           Name= d.NAME,

                       });

But I have an error that is Cannot implicitly convert type 'System.Linq.IQueryable' to bool.

I edited because "Problem Solved". For solution:

Download Install-Package System.Linq.Dynamic -Version 1.0.7 (said me @StepUp) then add to class

using System.Linq.Dynamic;

then following code like that,

 var whereCondition = db.FILTRE_LINKLER.Select(x => x.CONDITIONS).FirstOrDefault();
var product = (from d in db.PRODUCTS
                       where query
                       select new ProductModel
                       {
                           Description= d.DESCRIPTION,
                           Brand= d.BRANDS.BRAND,
                           SefUrl = sef_url,
                           Name= d.NAME,

                       }).Where(whereCondition);

Are you asking how to add conditions dynamically? Use the extension methods instead of the query syntax and pass whatever condition you want in the .Where() call. eg if (useColor) { query=query.Where(product=>product.Color='Red'); } — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 22, 2019 at 8:49
Yes, I want to add dynamically but it must come from Database because Conditions can change. It depends on Customers. One of them want to Stock>50 and others want to productName='test'. I want to write a code general usage. — AnilSengul
– AnilSengul, Commented Jan 22, 2019 at 8:56

Yasser Shaikh · Accepted Answer · 2019-01-22 08:53:02Z

1

What you are asking is to dynamically convert the conditions present in the database to where in linq. While there is no magic statement, that says use this. You would have to write an extension that is able to intrepret the conditions written in your database and returns a predicate which you could then use in your where clause.

answered Jan 22, 2019 at 8:53

Yasser Shaikh

47.9k50 gold badges208 silver badges290 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AnilSengul Over a year ago

can you give me an example ?

Yasser Shaikh Over a year ago

@AnilSengul you can pick some ideas from this article - codeproject.com/Tips/582450/…

StepUp · Accepted Answer · 2019-01-22 14:37:01Z

0

Try to use Dynamic LINQ. This is a sample of code using Dynamic LINQ library:

var query = northwind.Products
                         .Where("CategoryID = 3 AND UnitPrice > 3")
                         .OrderBy("SupplierID");

In your case:

var whereCondition = db.FILTRE_LINKLER.Select(x => x.CONDITIONS).FirstOrDefault();
var product = (from d in db.PRODUCTS
                   select new ProductModel
                   {
                       Description= d.DESCRIPTION,
                       Brand= d.BRANDS.BRAND,
                       SefUrl = sef_url,
                       Name= d.NAME,

                   })
              .Where(whereCondition);

UPDATE:

At first you should download a code to use Dynamic queries by this link.
Then use DynamicQueryable class from the project Dynamic Query
Then use your dynamic queries with IQueryable<T>.

Let me show an example:

var persons = new List<Person>()
{
    new Person(){Id = 1, FirstName = "1"},
    new Person(){Id = 2, FirstName = "2"},
    new Person(){Id = 3, FirstName = "3"}
};
var personWithIdTwo = persons
   .AsQueryable()
   .Where("Id==2");

UPDATE 1:

If you do not want to add class, then you can use an Expression Tree.

An example of an expression tree:

var propName = "STOCK"; // here you assign any value
var constValue = "50";  // here you assign any value
var param = Expression.Parameter(typeof(ProductModel), "p");
var exp = Expression.Lambda<Func<ProductModel, bool>>(
    Expression.GreaterThan(
       Expression.Property(param, propName),
       Expression.Constant(constValue)
    ),
    param
);
var product = (from d in db.PRODUCTS
                   where query
                   select new ProductModel
                   {
                       Description= d.DESCRIPTION,
                       Brand= d.BRANDS.BRAND,
                       SefUrl = sef_url,
                       Name= d.NAME,

                   }).Where(exp);

UPDATE 2:

You can download library Dynamic query through NuGet. So you should not create classes in your project:

edited Jan 22, 2019 at 14:37

answered Jan 22, 2019 at 9:46

StepUp

38.5k16 gold badges95 silver badges159 bronze badges

8 Comments

AnilSengul Over a year ago

did not work. "cannot convert from system.linq.expressions.expression to system.func<Model,bool>" error messages I got.

StepUp Over a year ago

@AnilSengul Please, see my updated answer. You should create class to work with dynamic queries.

AnilSengul Over a year ago

Just I want to add little thing. I have to add class for little thing. Are there other way to do this ?

AnilSengul Over a year ago

Property and Constant must be dynamic. These are in same string

AnilSengul Over a year ago

Yes it is enough for me. Problem solved. Thanks for answering. I shared solution above with editting my question.

|

Collectives™ on Stack Overflow

Get value from Database to Linq where clause

2 Answers 2

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related