Dynamic Query using Linq To SQL According Multiple Fields

Question

Hi Experts I have a special question About dynamic Linq to Sql. Consider we want to search in a table according two fields*(LetterNo(string) and LetterDate(Datetime))* .OK the problem is user can enter on of that fields or even both.

I searched in the internet and found "Linq.Dynamic" library in ScottGu weblog.but in that library if we want to use SqlParameter in exported command we should use @0 and param for that.problem is I don't know how many fields user entered.

I want use one query for that and no external tool like "Linq Kit PredicateBuilder".

If I create my query string Manually(and execute using ExecuteCommand) then I will abdicate SqlParameter and risk of Sql Injenction growing up. How Can do that? thanks

Answer 1

I suspect you are wanting to do something like the following:

IQueryable<Letter> query = context.Letters;
if (!string.IsNullOrEmpty(LetterNo))
    query = query.Where(letter => letter.LetterNo == LetterNo);
If (LetterDate.HasValue)
    query = query.Where(letter => letter.LetterDate == LetterDate);

When you execute query, it will combine the necessary expressions and issue a single query to the database based on the user's input.