Linq query on related entities
Tag : linq , By : Lord Zantor
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , this may be a very basic question but I haven't used Linq much so do need help. I want to retrieve a list of records based on the related record count. , You can check by using Count() <= 1 Linq style: from contact in context.ContactSet
where contact.Roles.Count() <= 1
select contact;
context.ContactSet.Where(c => c.Roles.Count() <= 1);
|
Is there any way to optimize this LINQ to Entities query?
Date : March 29 2020, 07:55 AM
will help you Using a let to reduce the number of r.First()'s will probably improve performance. It's probably not enough yet. var q = from r in ctx.Responses
.Where()
.GroupBy()
let response = r.First()
orderby response.User.AwardCity, response.Category.Label, r.Count() descending
select new
{
City = response.User.AwardCity,
Category = response.Category.Label,
Response = response.ResponseText,
Votes = r.Count()
};
|
LINQ to Entities query on related entities
Tag : linq , By : tjh0001
Date : March 29 2020, 07:55 AM
To fix the issue you can do This query is correct although its a cross-join - is simply the Cartesian product of two sets -. You can explicitly specify a join based on equality condition. var query = from par in context.Parents
join child in context.Childrens on child.ParentID equals par.ID
join pet in context.Products on child.ID equals pet.ChildID
Where par.parent_id = 1
select new { par.Name, par.Id };
|
sqlite query slow, how to optimize (using linq to entities)
Tag : chash , By : Grace Jones
Date : March 29 2020, 07:55 AM
wish helps you The biggest optimization would be changing the Contains to a StartsWith. Which would be equivalent to changing from name like '%search%' to name like 'search%'. Otherwise SQLite can't fully use the indexes you placed on the columns and you are basically searching the whole table. // First Name
if (!string.IsNullOrEmpty(firstName))
{
firstName = firstName.Trim().ToUpper();
records = records.Where(x => x.FirstName.StartsWith(firstName));
}
|
How to optimize a simple LINQ query with two related tables?
Date : March 29 2020, 07:55 AM
Hope that helps I have two related tables with the following structure: , Starting over assuming everything in memory only... var cured_receptions = new HashSet<int>((from r in receptions where r.ReceptionStart < endDateTime select r.PatientId));
var cured_patients = from p in patients where cured_receptions.Contains(p.Id) select p;
var rand = new Random();
var begin = DateTime.Now;
var receptions = Enumerable.Range(1, 100000).SelectMany(pid => Enumerable.Range(1, rand.Next(0, 100)).Select(rid => new { PatientId = pid, ReceptionStart = begin.AddDays(-rand.Next(1, 180)) })).ToList();
var patients = Enumerable.Range(1, 100000).Select(pid => new { Id = pid, Surname = string.Format("Smith{0}", pid) }).ToList();
var startTime = Util.ElapsedTime;
var endDateTime = new DateTime(2017, 5, 1);
//var cured_receptions = (from r in receptions where r.ReceptionStart < new DateTime(2017, 5, 1) select r.PatientId).Distinct().ToList();
//var cured_receptions = (from r in receptions where r.ReceptionStart < new DateTime(2017, 5, 1) select r.PatientId).Distinct();
//var cured_receptions = new HashSet<int>((from r in receptions where r.ReceptionStart < new DateTime(2017, 5, 1) select r.PatientId).Distinct());
//var cured_receptions = new HashSet<int>((from r in receptions where r.ReceptionStart < endDateTime select r.PatientId).Distinct());
//var cured_receptions = new HashSet<int>((from r in receptions where r.ReceptionStart < new DateTime(2017, 5, 1) select r.PatientId));
var cured_receptions = new HashSet<int>((from r in receptions where r.ReceptionStart < endDateTime select r.PatientId));
var cured_patients = from p in patients where cured_receptions.Contains(p.Id) select p;
// var cured_patients = (from r in receptions
// where r.ReceptionStart < endDateTime
// join p in patients on r.PatientId equals p.Id
// select p).Distinct();
// var cured_patients = from p in patients
// join r in receptions on p.Id equals r.PatientId into rj
// where rj.Any(r => r.ReceptionStart < endDateTime)
// select p;
cured_patients.Count().Dump();
var endTime = Util.ElapsedTime;
(endTime - startTime).Dump("Elapsed");
|
Boards Message :
Related QUESTIONS :