CODEWITHSUNDEEP
linqlinq-to-sqllinq-to-nhibernate
Saad
Saad

Reputation: 1352

Linq ISNULL functionality

i have a query in SQL

Declare @Id Int
set @UserName = Null
set @FullName = Null
Select * from tblPermission where (UserName= @UserName OR @UserName IS NULL) && (
FullName = @FullName OR @FullName IS NULL)

i m using Fluent nHibernate as ORM .

i m trying this:

 var Allusers = from u in session.Query<User>()
                               where u.UserName.Contains(UserName) || UserName == null
                               && u.FullName.Contains(FullName) || FullName == null
                               select u;

This linq query works fine for UserName but not working if UserName and FullName filters both have some value. how to achieve this functionality in LINQ ? Any Idea?

Thanks

Upvotes: 1

Views: 2006

Answers (2)

Julien Lebosquain
Julien Lebosquain

Reputation: 41243

I would go with dynamically building the query:

var Allusers = session.Query<User>();
if (UserName != null)
    Allusers = Allusers.Where(u => u.UserName.Contains(UserName));
if (FullName != null)
    Allusers = Allusers.Where(u => u.FullName.Contains(FullName));

Since it's a query, it won't be executed until you enumerate it so chaining Where calls won't execute it multiple times. Plus you'll get a SQL query optimized for each case.

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1500785

EDIT: Completely new answer now that we've got more details...

I suspect this is just a problem of precedence. Express it explicitly:

Allusers = from u in session.Query<User>()
           where (u.UserName.Contains(UserName) || UserName == null)
                 && (u.FullName.Contains(FullName) || FullName == null)
           select u;

Upvotes: 2

Related Questions