本文是小编为大家收集整理的关于通过Linq从DbSet对象中更新集合的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。
问题描述
我知道这并不复杂,但我为之挣扎.
我有IList<Material>收集
public class Material { public string Number { get; set; } public decimal? Value { get; set; } } materials = new List<Material>(); materials.Add(new Material { Number = 111 }); materials.Add(new Material { Number = 222 });
我有DbSet<Material>收集 使用列编号和 valuecolumn
我需要基于DbSet<Material>收集,但有以下条件
更新IList<Material>值属性IList<Material>- 数据库中只有一个查询请求
- 数据库返回的数据必须受数字标识符的限制(请勿将整个数据库表加载到内存中)
我尝试了关注(基于我以前的 Question )
)工作解决方案1 ,但将整个表下载到内存中(在SQL Server Profiler中监视).
var result = ( from db_m in db.Material join m in model.Materials on db_m.Number.ToString() equals m.Number select new { db_m.Number, db_m.Value } ).ToList(); model.Materials.ToList().ForEach(m => m.Value= result.SingleOrDefault(db_m => db_m.Number.ToString() == m.Number).Value);
工作解决方案2 ,但它对集合中的每个项目执行查询.
model.Materials.ToList().ForEach(m => m.Value= db.Material.FirstOrDefault(db_m => db_m.Number.ToString() == m.Number).Value);
不完全解决方案,我尝试使用的其中包含方法
// I am trying to get new filtered collection from database, which i will iterate after. var result = db.Material .Where(x=> // here is the reasonable error: cannot convert int into Material class, but i do not know how to solve this. model.Materials.Contains(x.Number) ) .Select(material => new Material { Number = material.Number.ToString(), Value = material.Value});
有什么想法吗?对我来说,以逗号分隔的ID值作为参数并直接获取数据,执行存储过程要容易得多,但我也想掌握LINQ.
推荐答案
我会做这样的事情,而不会太可爱:
var numbersToFilterby = model.Materials.Select(m => m.Number).ToArray(); ... var result = from db_m in db.Material where numbersToFilterBy.Contains(db_m.Number) select new { ... }
问题描述
i know it is not complicated but i struggle with it.
I have IList<Material> collection
public class Material { public string Number { get; set; } public decimal? Value { get; set; } } materials = new List<Material>(); materials.Add(new Material { Number = 111 }); materials.Add(new Material { Number = 222 });
And i have DbSet<Material> collection with columns Number and ValueColumn
I need to update IList<Material> Value property based on DbSet<Material> collection but with following conditions
- Only one query request into database
- The returned data from database has to be limited by Number identifier (do not load whole database table into memory)
I tried following (based on my previous question)
Working solution 1, but download whole table into memory (monitored in sql server profiler).
var result = ( from db_m in db.Material join m in model.Materials on db_m.Number.ToString() equals m.Number select new { db_m.Number, db_m.Value } ).ToList(); model.Materials.ToList().ForEach(m => m.Value= result.SingleOrDefault(db_m => db_m.Number.ToString() == m.Number).Value);
Working solution 2, but it execute query for each item in the collection.
model.Materials.ToList().ForEach(m => m.Value= db.Material.FirstOrDefault(db_m => db_m.Number.ToString() == m.Number).Value);
Incompletely solution, where i tried to use contains method
// I am trying to get new filtered collection from database, which i will iterate after. var result = db.Material .Where(x=> // here is the reasonable error: cannot convert int into Material class, but i do not know how to solve this. model.Materials.Contains(x.Number) ) .Select(material => new Material { Number = material.Number.ToString(), Value = material.Value});
Any idea ? For me it is much easier to execute stored procedure with comma separated id values as a parameter and get the data directly, but i want to master linq too.
推荐答案
I'd do something like this without trying to get too cute :
var numbersToFilterby = model.Materials.Select(m => m.Number).ToArray(); ... var result = from db_m in db.Material where numbersToFilterBy.Contains(db_m.Number) select new { ... }