CODEWITHSUNDEEP
c#databaselinqlambdamany-to-many
RazAquato
RazAquato

Reputation: 11

LINQ/lambda: How can I query a DB table based on information from another table? (many to many relationship)

I have a database scheme with 3 tables. One for requisitions, one for hospitals, and one joining the two (many-to-many relationship).

I'd like to list all requisitions in the database that are linked to a selected hospital.

This is what I have so far:

var valgtSykehus = Db.Sykehus.Where(n => n.Navn == sykehus).Single(); //this gives me a variable with my current hospital. I want to list all requistions that contains this.

var Rekvisisjoner = Db.Rekvisisjoner
                    .Where(r => r.Arkivert == true) //get only archived requsitions
                    .Include(p1 => p1.Sykehus) //include hospitals
                    .ToList() //this generates a list of -all- requisitions with the hospitals they are attached to.
                    .Where(x => x.Created > DateTime.Now.AddYears(-3)) /only go 3 years back
                    .Where(x => x.Sykehus.Contains(valgtSykehus)); //here is the problem. I want to discard all requisitions that does NOT contain the hospital in the valgtSykehus variable

Anyway, this gives me zero requistions, but if I skip the last line, I get all archived requistions.

Upvotes: 1

Views: 390

Answers (3)

RazAquato
RazAquato

Reputation: 11

Oh my. I just realised that none of the archived requisitions contains any connections to the hospitals, as they apparently are removed one-by-one when the requisition is processed in the program.

I figured this out while trying to reverse the query, so thanks for that tip.

Upvotes: 0

Ivan Stoev
Ivan Stoev

Reputation: 205599

x.Sykehus.Contains(valgtSykehus) executes in LINQ to Objects context (due to the intermediate ToList call) and most likely uses reference equality, which normally should work as soon as you use tracking queries.

Still, it's safer and also more efficient to do the whole thing with a single db query using Any condition with primitive key. Something like this:

var Rekvisisjoner = Db.Rekvisisjoner
    .Include(r => r.Sykehus) //include hospitals
    .Where(r => r.Arkivert == true) //get only archived requsitions the hospitals they are attached to.
    .Where(r => r.Created > DateTime.Now.AddYears(-3)) /only go 3 years back
    .Where(r => r.Sykehus.Any(s => s.Navn == sykehus));

If there is an issues with using DateTime.Now.AddYears(-3) inside the query, just put into variable outside of the query and use it inside.

var minDate = DateTime.Now.AddYears(-3);
var Rekvisisjoner =
   // ...
   .Where(r => r.Created > minDate)
   //...

Upvotes: 1

Paul Kertscher
Paul Kertscher

Reputation: 9713

The issue may lie in the implementation of Contains. Contains has to check equality somehow. Anyway, if your valgtSykehus object is logically contained in x.Sykehus (i.e. has the same data), but not exactly the same object (i.e. the same reference), it's possible that Contains fails to find it, due to the default implementation of == in reference types (== is true, if the objects are exactly the same reference, false otherwise, even though all the data is the same).

You could try the following:

var Rekvisisjoner = Db.Rekvisisjoner
                    .Where(r => r.Arkivert == true)
                    .Include(p1 => p1.Sykehus)
                    .ToList() 
                    .Where(x => x.Created > DateTime.Now.AddYears(-3)) 
                    .Where(x => x.Sykehus.Any(sh => sh.Id == valgtSykehus.Id));

If Id (or whatever your ID property is named) is a value field (most likely) this will return true whenever the ID of an Sykehus matches the ID of valgtSykehus.

Upvotes: 0

Related Questions