通过Linq从DbSet对象中更新集合

本文是小编为大家收集整理的关于通过Linq从DbSet对象中更新集合的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
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> 集合Number 和 ValueColumn

列

我需要根据 DbSet<Material> 集合更新 IList<Material> 值属性，但需要满足以下条件

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

我尝试关注(基于我之前的问题)

工作解决方案 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);

不完全解决方案，我尝试使用 contains 方法

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

本文地址：https://www.itbaoku.cn/post/1557201.html

百度翻译此文有道翻译此文

问题描述

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.