Click here to Skip to main content
15,038,237 members
Please Sign up or sign in to vote.
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
Updated 9-Jul-20 10:26am
v6

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.
   
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.
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.
   
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?
Fixed it by adding If else conditions. Thank you all .
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900