Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to build up a Linq to Sql where clause bit by bit?

Tags:

c#

linq-to-sql

I am being passed a set of querystring parameters within a Parameters class with which to query an image database. With each call some parameters may by null. So in sql I would build up the query like

if (parameters.Value1 != null)
{
    sql.Append("sql_where_clause");
}

if (parameters.Value2 != null)
{
    sql.Append("sql_where_clause");
}

How do I do the same using Linq?

like image 964
Nick Allen Avatar asked May 22 '09 14:05

Nick Allen


People also ask

What is where clause in LINQ?

The where clause is used in a query expression to specify which elements from the data source will be returned in the query expression. It applies a Boolean condition (predicate) to each source element (referenced by the range variable) and returns those for which the specified condition is true.

How LINQ queries converted into SQL queries?

LINQ to SQL translates the queries you write into equivalent SQL queries and sends them to the server for processing. More specifically, your application uses the LINQ to SQL API to request query execution. The LINQ to SQL provider then transforms the query into SQL text and delegates execution to the ADO provider.

Which is faster SQL or LINQ?

Sql is faster than Linq. Its simple: if I m executing a sql query directly its a one way process whereas if I m using linq, first its been converted to sql query and then its executed.

Is LINQ to SQL still used?

LINQ to SQL was the first object-relational mapping technology released by Microsoft. It works well in basic scenarios and continues to be supported in Visual Studio, but it's no longer under active development.


2 Answers

The best way to dynamically build where-clauses is to use the wonderful Albahari PredicateBuilder.

You can use this to build where-clause expressions containing OR as well as AND. Language-integrated support for this was originally intended but didn't quite make it into C# 3.

For example:

var whereClause = PredicateBuilder.False<Customer>();

if (parameters.Value1 != null)
{
    whereClause = whereClause.Or(customer => customer.City == parameters.Value1);
}

var query = db.Customers.Where(whereClause);
like image 111
Pete Montgomery Avatar answered Feb 13 '23 11:02

Pete Montgomery


easy, IQueryables aren't evaluated until you enumerate, so simply keep tagging on where clauses.

if (parameters.Value1 != null)
{
    results = results.Where(x => <some condition>);
}

if (parameters.Value2 != null)
{
    results = results.Where(x => <some other condition>);
}
like image 43
Andrew Bullock Avatar answered Feb 13 '23 10:02

Andrew Bullock