Or condition is not working in where clause og LINQ query

Question

1.00/5 (1 vote)

See more:

Hi,

I am using below query to get the employee detail - If there is any fulltime employee exist we need to display that employee if not need to pull default employee.

var employees = new List<employee>();

var fulltimeemployees= db.Table<fulltimeemp>().Where(fl => siteId == null || fl.SiteId == siteId).Select(e => e.EmployeeId);

employees = db.Table<employee>().Where(e => fulltimeemployees.Contains(e.Id) || e.IsDefault).ToList();

return employees ;

But some how even though first condition exist (which means we have full time employee exist) but still it is getting default employee too. and displaying both employee information which is wrong.

I am new to SQL , so just want to check what is wrong with above query ? Can anyone help me on this please ?

Thank you in advance.

What I have tried:

I tried below query too

employees  = db.Table<employee>().Where(e => fulltimeemployees.Where(x=>x.EmployeeId==e.Id).Any() || e.IsDefault).ToList();

but i am having issue at

x.EmployeeId

-- below error message I am getting.

'Guid' does not contain a definition for 'EmployeeId' and no accessible extension method 'EmployeeId' accepting a first argument of type 'Guid' could be found (are you missing a using directive or an assembly reference?)

Posted 6-Jul-20 9:08am

chinnuTest

Updated 9-Jul-20 10:26am

v6

Add a Solution

3 solutions

Solution 1

Or doesn't mean what you think it does: is means "either one", not "one or the other".

So if you have a list of mixed "a", "b", and "c", then "a OR b" will return all the "a"s and all the "b"s, but no "c"s.

To do what you want, you would have to run two queries: one to fetch all "a"s and then only if that returns zero rows then a second query to return the "b"s

And by the way: that isn't SQL, it's a Linq query and stays entirely in your app once the db.Table call has returned.

Posted 6-Jul-20 9:50am

OriginalGriff

Comments

F-ES Sitecore 7-Jul-20 6:07am

The "IsDefault" probably can't be converted to a SQL clause which is why it is being done that way.

Solution 2

Seems you want to use join to get details from "master table"!

See:
join clause - C# Reference | Microsoft Docs[^]
Perform left outer joins (LINQ in C#) | Microsoft Docs[^]

C#

var result = (from fte in fulltimeemployees
    join emp in db.Table<Employee>().Where(e=> e.IsDefault==false) on fte.id equals emp.id into grp
    from item in grp.DefaultIfEmpty()
    select new 
    {
        fte.id,
        //other details here ...
    })
    .ToList();

Note: the description of issue is not quite clear. So, i can be wrong.

Posted 6-Jul-20 22:56pm

Maciej Los

Comments

chinnuTest 7-Jul-20 14:21pm

Hi,

Thank you for helping me on this, but I am having issue at fte.id even I tried fte.EmployeeId -- It saying Guid doesn't have a definition for EmployeeId…. Sorry I didn't provide full information earlier updated query in my post. can you please help me ?

var employees = new List<employee>();

var fulltimeemployees= db.Table<fulltimeemp>().Where(fl => siteId == null || fl.SiteId == siteId).Select(e => e.EmployeeId);

employees = db.Table<employee>().Where(e => fulltimeemployees.Contains(e.Id) || e.IsDefault).ToList();

return employees ;

chinnuTest 8-Jul-20 10:21am

Can you please any one help me on this ?

chinnuTest 8-Jul-20 12:51pm

Sorry I did update the question completely. I am having issue at fte.id I did modified it to fte.EmployeeId but still it is not taking it.

'Guid' does not contain a definition for 'EmployeeId' and no accessible extension method 'EmployeeId' accepting a first argument of type 'Guid' could be found (are you missing a using directive or an assembly reference?)

Maciej Los 8-Jul-20 13:14pm

How should i know if do not see your database structure and data?
My best guess is:

employees = db.Table<employee>()
    .Where(e => fulltimeemployees.Any(fe=> fe.EmployeeId ==e.Id) || e.IsDefault)
    .ToList();

chinnuTest 8-Jul-20 17:02pm

I know with out knowing DB structure it's tough , Thank you for your help so far. but the above query I tried but It's not working when I debug query breaking at run time..

employees = db.Table<employee>()
.Where(e => fulltimeemployees.Any(fe=> fe.EmployeeId ==e.Id) || e.IsDefault)
.ToList();

chinnuTest 9-Jul-20 12:11pm

var fulltimeemployees= db.Table<fulltimeemp>().Where(fl => siteId == null || fl.SiteId == siteId).ToList();

employees = db.Table<employee>()
.Where(e => fulltimeemployees.Any(fe=> fe.EmployeeId ==e.Id) || e.IsDefault)
.ToList();

Sorry, but posting one last time if you can see any mistake please let me know. These are two queries I am using but code is breaking at second one when it is executing. Is there anything wrong here ?

Thank you so much..

Maciej Los 9-Jul-20 13:55pm

How could i know?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chinnuTest · Accepted Answer · 2020-07-09T10:26:00

Solution 3

Fixed it by adding If else conditions. Thank you all .

Posted 9-Jul-20 10:26am

chinnuTest