Query works in MSSQL, but not in LINQ

Question

I have a method that receives a name of either a Group or a User (email) and has to return the Abstract_Entity object of the Group or User.

The MSSQL query seems to work in all cases, but the only time the LINQ query works is when the string is the email of a user.

TranslateUserOrGroup(string name): Abstract_Entity

Do you guys see any difference in the MSSQL query and the LINQ code?

The LINQ query

return (from e in complementCoinsEntities.Abstract_Entity.AsNoTracking()
     join eg in complementCoinsEntities.Entity_Group on e.ID equals eg.entityID
     join g in complementCoinsEntities.Group on eg.groupID equals g.ID
     join u in complementCoinsEntities.User on e.ID equals u.userID
     where userOrGroup == u.email || userOrGroup == g.name 
     select e).SingleOrDefault();

The MSSQL query

select DISTINCT ae.ID
from Abstract_Entity ae
join Entity_Group eg on ae.ID = eg.entityID
join [Group] g on eg.groupID = g.ID
join [User] u on ae.ID = u.userID
where g.name = 'test' OR u.email = 'test'

enter image description here

Not entirely relevant question: is there a reason for not doing it like complementCoinsEntities.Abstract_Entity.Where(x => x.Entity_Group.Group.name == userOrGroup || x.Entity_Group.Group.User.email == userOrGroup).SingleOrDefault();? — Patryk Ćwiek
– Patryk Ćwiek, Commented Dec 5, 2013 at 15:56
Have you checked (using profiler, or EF tools) what SQL is generated from your LINQ query? — Jamiec
– Jamiec, Commented Dec 5, 2013 at 16:00
@Kuzgun Yes, it has the same result. The user-side works but the group-side does not — David
– David, Commented Dec 5, 2013 at 16:02

Richthofen · Accepted Answer · 2013-12-05 16:31:34Z

2

I would start by peeling away the SingleOrDefault and ToList() the results in the Immediate Window to see what query results are being returned. It's likely you're stripping away matching rows trying to use SingleOrDefault instead of DISTINCT. They do not function the same. So take away 'DISTINCT' in your SQL query and remove the SingleOrDEfault call, and assign to an object and run ToList and put a breakpoint. You should see what's different.

Specifically: SingleOrDefault may key on multiple results and you are not guaranteed to get any specific result back (including a null result).

edited Dec 5, 2013 at 16:31

answered Dec 5, 2013 at 16:08

Richthofen

2,07619 silver badges40 bronze badges

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

Comments

Emanuele Greco · Accepted Answer · 2013-12-05 16:14:04Z

0

.SingleOrDefault()

Returns the only element of a sequence, or a default value if the sequence is empty; this method throws an exception if there is more than one element in the sequence.

have you tried .FirstOrDefault() ?

answered Dec 5, 2013 at 16:14

Emanuele Greco

12.8k7 gold badges55 silver badges72 bronze badges

Collectives™ on Stack Overflow

Query works in MSSQL, but not in LINQ

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related