I'm new to EF\LINQ. How to rewrite such sql query in LINQ:
SELECT Pacient_ID FROM VisitDates WHERE ID in
(SELECT visitDate_ID from Reviews WHERE comments LIKE N'%name%')
Problem is that I can't access colums created by EF in LINQ query (visitDate_ID, Pacient_ID) I'v tried to do something like this, but it is very slow:
List<Pacient> found = new List<Pacient>();
List<Pacient> pacients = db.Pacients.Include(p => p.visits.Select(w => w.reviews)).ToList();
bool k = false;
foreach (Pacient p in pacients)
{
foreach (VisitDate date in p.visits)
{
foreach (Review r in date.reviews)
{
if (r.comments.ToLower().Contains(name.ToLower()))
{
found.Add(p);
k = true;
break;
}
}
if (k)
{
k = false;
break;
}
}
}
Thanks!
This will do what your SQL query asked for:
var results=db.VisitDates
.Where(vd=>vd.Reviews.Any(r=>r.comments.Contains("name")))
.Select(vd=>vd.Pacient_ID);
.ToLower() stuff isn't required (usually) because the comparison is handled by the database, which by default does case insensitive comparisons, and adding them may cause the query to run significantly slower.
If you want pacients, then it'd be:
var results=db.Pacients
.Where(p=>p.VisitDates.Any(vd=>vd.Reviews.Any(r=>r.comments.Contains("name"))));
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments