linq with null condition

Question

How can the foll. sql be written in Linq. I am using LinqToEntities,C#. @companyID & @branchID are the parameters

select * from tblEmp e
where e.deleted = 0 and
(e.companyId = @companyID OR e.companyid is null) and
(e.branchId = @branchID OR e.branchid is null)

For now, there is a stored procedure for the same and I am using it in linq, like this:

var qry = from d in MYDB.GetData(int companyid, int branchid)
select new
{
//all reqd. columns...
}

so, is it possible to write the above in Linq, directly.

EDIT:

public IEnumerable<tblEmp> GetData(Guid gCID, Guid gBID)
{
  var employees = (from e in tblEmp
                 where !e.deleted
                 && (e.companyId == gCID || e.companyid == null)
                 && (e.branchId == gBID || e.branchid == null)
                 select e
                 ).AsEnumerable();
}

//Both Parameters are '00000000-0000-0000-0000-000000000000'. I am expecting all records but the count is 0. Where am I going wrong.

Silvermind · Accepted Answer · 2013-12-15 09:10:31Z

2

Sure:

var employees = (from e in tblEmp
                 where !e.deleted
                 && (e.companyId == aCompanyIdHere || e.companyid == null)
                 && (e.branchId == aBranchIdHere || e.branchid == null)
                 select new
                 {
                     e.Id,
                     e.companyId,
                     e.branchId
                 });

Edit

The query should work, but I suspect you requirements are a bit different. If you want to get all items when a passed in parameter Guid is empty you could do the following.

public IEnumerable<tblEmp> GetData(Guid gCID, Guid gBID)
{
    var employees = (from e in tblEmp
                     where !e.deleted
                     && (gCID == Guid.Empty || e.companyid == gCID)
                     && (gBID == Guid.Empty || e.branchid == gBID)
                     select e
                     ).AsEnumerable();
}

But for better readability IMO:

public IEnumerable<tblEmp> GetData(Guid? gCID, Guid? gBID)
{
    var employees = (from e in tblEmp
                     where !e.deleted
                     && (gCID == null || e.companyid == gCID)
                     && (gBID == null || e.branchid == gBID)
                     select e
                     ).AsEnumerable();
}

and pass in null instead of Guid.Empty if you want all records.

var data = GetData(null, null);

edited Dec 15, 2013 at 9:10

answered Dec 15, 2013 at 8:25

Silvermind

5,9943 gold badges27 silver badges47 bronze badges

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

2 Comments

Ruby Over a year ago

Thank you. Will this work if the datatype is guid instead of int. There are records in DB, but I get 0 count. I have updated my Question what I have tried.

Silvermind Over a year ago

@Ruby No problem, it really depends on your needs. I'm more concerned about the caller here :) GetData(Guid.Empty, Guid.Empty) vs GetData(null, null), but to each his own I guess :)

Enigmativity · Accepted Answer · 2013-12-15 08:37:55Z

1

var qry = (
    from test in MYDB.tblEmp
    where !e.deleted
        && (e.companyId == "any id" || e.companyid == null)
        && (e.branchId == "anyid" || e.branchid == null)
    select test
).FirstOrDefault();

edited Dec 15, 2013 at 8:37

Enigmativity

117k12 gold badges101 silver badges184 bronze badges

answered Dec 15, 2013 at 8:29

Sayantan Das

711 gold badge1 silver badge16 bronze badges

1 Comment

Silvermind Over a year ago

Perhaps you're accidentally right, but I don't see a TOP 1 in his query anywhere.

Collectives™ on Stack Overflow

linq with null condition

2 Answers 2

Edit

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related