使用LINQ查询，其中Context匹配对象列表中的多个参数 [英] Query with LINQ where Context matches multiple parameters from a List of Objects

问题描述

假设我有一个包含多个列的表格"Article"，例如:

Presumed I have a table "Article" with multiple columns, e.g.:

CREATE TABLE dbo.Article (
  Id int NOT NULL,
  ProducerId INT NOT NULL,
  Barcode nvarchar(50) NOT NULL,
  DescriptionText nvarchar(50) NOT NULL,
  ActiveFlag BIT NOT NULL
)

在我的ASP.NET Core应用程序中，我使用LINQ查询该表，例如:

In my ASP.NET Core application I am using LINQ to query that table, e.g.:

IQueryable<Article> query = _context.Article
                     .Where( p => p.Active == true );

那当然可以.现在，我得到一些参数，这是一个非常简单的对象，具有 List<ArticleQuery> 或 IEnumerable<ArticleQuery> .

That works of course. Now I am getting some parameter, which is a very simple Object with a List < ArticleQuery > or alternativly a IEnumerable < ArticleQuery >.

ArticleQuery.cs:

ArticleQuery.cs:

public class ArticleQuery
{
    [Required]
    public IEnumerable<ArticleRequest> Parts { get; set; }
}

public class ArticleRequest
{
    public int ProducerId { get; set; }
    public string Barcode { get; set; }
}

实际上，我不知道如何将其集成到LINQ中.我尝试了很多东西，但最终我还是没想到要得到这样的连接:

Actually I have no clue how to integrate that into LINQ. I tried lots of stuff, but in the end I never figured out to get something like this join:

IQueryable<Article> query = _context.Article
    .Join(articleQuery.ArticleRequest,
        x => new { a = x.Barcode, b = x.Barcode},
        y => new { a = y.ProducerId, b = y.ProducerId},
        (x, y) => x);

此伪代码也不起作用(但Join似乎是更好的尝试):

Also this pseudo-code didn't work (but the Join seems to be the better try):

IQueryable<Article> query = _context.Article.Where( p => 
   p.Active == true &&
   articleQuery.ArticleRequest.ProducerId.Contains(p.ProducerId) && 
   articleQuery.ArticleRequest.Barcode.Contains(p.Barcode)
);

有什么想法可以正常工作吗?

Any ideas how that works correctly?

推荐答案

我自己解决了这个问题.并不是那么完美，但是可以正常工作.

I solved the problem by myself. Not that perfect, but it's working.

我为Web服务使用表"RequestArticles"创建了一个新数据库:

I created a new database for my webservice with a table "RequestArticles":

CREATE TABLE dbo.RequestArticle(
    RequestId UNIQUEIDENTIFIER NOT NULL,
    ProducerId INT NOT NULL,
    Barcode NVARCHAR(30) NOT NULL
)

我将请求写入该表:

Guid requestGuid = Guid.NewGuid();
foreach (var requestArticle in requestedArticles.Articles) requestArticle.RequestId = requestGuid;
_context.RequestArticle.AddRange(requestedArticles.Articles); //IEnumerable list of articles
_context.SaveChanges();

之后，我只是手动加入了新的请求表.

After that I just manually joined my new request-table.

IQueryable<Articles> query = _context.Articles.FromSql<Articles>(
    @"SELECT      a.*, ra.Barcode AS RequestedBarcode
      FROM        dbo.Articles a
      INNER JOIN  Webservice.dbo.RequestArticle ra
              ON  ra.ProducerId = a.ProducerId AND ra.Barcode = a.Barcode
      WHERE       ra.RequestId = {1}",
    requestGuid);

此刻，我正用与我的新上下文不同的字符串替换SQL-String中的新静态数据库名称，但基础知识很快就发挥了作用.尽管这不是我所希望的解决方案，但我认为LINQ + EF比这更聪明.:-)

At the moment I am suffering to replace the new static database-name within the SQL-String with a different string from my new context, but the basics work fast like a charm. Even though it's not the solution I was hoping for, as I thought LINQ+EF are smarter than that. :-)

这篇关于使用LINQ查询，其中Context匹配对象列表中的多个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！