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

本文是小编为大家收集整理的关于通过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> 集合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.

推荐答案

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