通过Linq从DbSet对象更新集合 [英] Update collection from DbSet object via Linq

问题描述

我知道这并不复杂，但是我为此感到困惑.

i know it is not complicated but i struggle with it.

我有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>个收藏 具有列 Number 和 ValueColumn

And i have DbSet<Material> collection with columns Number and ValueColumn

我需要基于DbSet<Material>集合但具有以下条件来更新IList<Material> Value属性

I need to update IList<Material> Value property based on DbSet<Material> collection but with following conditions

• 仅一个查询请求进入数据库
• 从数据库返回的数据必须受数字标识符限制(不要将整个数据库表加载到内存中)

我尝试了以下操作(基于我之前的问题)

I tried following (based on my previous question)

工作解决方案1 ​​，但将整个表下载到内存中(在sql server profiler中监视).

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);

工作解决方案2 ，但是它对集合中的每个项目执行查询.

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});

有什么主意吗?对我来说，以逗号分隔的id值作为参数执行存储过程并直接获取数据要容易得多，但是我也想掌握linq.

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 { ... }

这篇关于通过Linq从DbSet对象更新集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！