需要更高效的LINQ语句或使用实体框架查询SQL Server CE的方法

本文关键字：查询 框架 实体 SQL CE Server 方法 高效 LINQ 语句 | 更新日期: 2023-09-27 18:28:17

我有一个方法，该方法使用服务点应用程序的实体框架查询SQL Server CE数据库并打印结果。我使用的代码是有效的，但当数据库的大小增加时，该方法需要更长的时间来执行。我认为这是因为数据库被多次查询以获得所需的结果。我真正想做的是查询数据库一次并将结果存储在内存中，然后对存储在内存的数据运行所有其他查询。

该方法主要需要查询数据库以查找今天的交易（ZTotaled==null），然后每30分钟打印一次交易日的交易数量和总销售额。

我正在使用的代码如下。。。

var ztotal = from z in context.Transactions
where z.ZTotaled == null
select new { z.NumerPeople, z.PricePaid, z.ZTotaled, z.DateTime }; 

var ztotal2 = from z in ztotal
where z.ZTotaled == null && z.DateTime.Hour == 18 && z.DateTime.Minute < 30
select new { z.NumerPeople, z.PricePaid };

totalPeople = ztotal2.Sum(o => o.NumerPeople);
totalPaid = ztotal2.Sum(o => o.PricePaid);

sb2.Append(string.Format("{0}{1,10}{2,16}", "18.00 - 18.30", totalPeople.ToString(), totalPaid.ToString()) + Environment.NewLine);
ztotal2 = from z in ztotal
where z.ZTotaled == null && z.DateTime.Hour == 18 && z.DateTime.Minute >= 30
select new { z.NumerPeople, z.PricePaid };
totalPeople = ztotal2.Sum(o => o.NumerPeople);
totalPaid = ztotal2.Sum(o => o.PricePaid);
sb2.Append(string.Format("{0}{1,10}{2,16}", "18.30 - 19.00", totalPeople.ToString(), totalPaid.ToString()) + Environment.NewLine);
ztotal2 = from z in ztotal
where z.ZTotaled == null && z.DateTime.Hour == 19 && z.DateTime.Minute < 30
select new { z.NumerPeople, z.PricePaid };
totalPeople = ztotal2.Sum(o => o.NumerPeople);
totalPaid = ztotal2.Sum(o => o.PricePaid);
sb2.Append(string.Format("{0}{1,10}{2,16}", "19.00 - 19.30", totalPeople.ToString(), totalPaid.ToString()) + Environment.NewLine);
ztotal2 = from z in ztotal
where z.ZTotaled == null && z.DateTime.Hour == 19 && z.DateTime.Minute >= 30
select new { z.NumerPeople, z.PricePaid };
totalPeople = ztotal2.Sum(o => o.NumerPeople);
totalPaid = ztotal2.Sum(o => o.PricePaid);
sb2.Append(string.Format("{0}{1,10}{2,16}", "19.30 - 20.00", totalPeople.ToString(), totalPaid.ToString()) + Environment.NewLine);
ztotal2 = from z in ztotal
where z.ZTotaled == null && z.DateTime.Hour == 20 && z.DateTime.Minute < 30
select new { z.NumerPeople, z.PricePaid };
totalPeople = ztotal2.Sum(o => o.NumerPeople);
totalPaid = ztotal2.Sum(o => o.PricePaid);
sb2.Append(string.Format("{0}{1,10}{2,16}", "20.00 - 20.30", totalPeople.ToString(), totalPaid.ToString()) + Environment.NewLine);

有人能告诉我如何提高代码的效率吗？

非常感谢

James

需要更高效的LINQ语句或使用实体框架查询SQL Server CE的方法

我认为我遇到的性能问题是由于延迟执行。通过将.ToList（）添加到第一个LINQ查询中，它被迫立即执行对象列表中的查询。然后，针对内存中的该列表执行所有进一步的查询。

var ztotal = (from z in context.Transactions
where z.ZTotaled == null
select new { z.NumerPeople, z.PricePaid, z.ZTotaled, z.DateTime }).ToList(); 

var ztotal2 = from z in ztotal
where z.ZTotaled == null && z.DateTime.Hour == 18 && z.DateTime.Minute < 30
select new { z.NumerPeople, z.PricePaid };

totalPeople = ztotal2.Sum(o => o.NumerPeople);
totalPaid = ztotal2.Sum(o => o.PricePaid);

sb2.Append(string.Format("{0}{1,10}{2,16}", "18.00 - 18.30", totalPeople.ToString(),      totalPaid.ToString()) + Environment.NewLine);
相关文章：