EF核心查询通过过滤进行多对多查询 [英] EF Core Query Many to Many with filtering

问题描述

我具有以下表格结构:

我想检索提供的 reportId 的所有资金.我是这样做的:

I want to retrieve all funds for provided reportId. I did it this way:

                var result = _context.FundsInReports
                        .Join(_context.Funds,
                                a=> a.FundId,
                                b => b.Id,
                                (fir, fund) => new {fir, fund})
                        .Join(_context.Reports,
                                a=> a.fir.ReportId,
                                b=> b.Id,
                                (fir2, report) => new { fir2, report})
                        .Where(q=> q.fir2.fir.ReportId==reportId)
                        .Select(res => new FundsResponse()
                        {
                            FundId = res.fir2.fund.Id,
                            LegalName = res.fir2.fund.LegalName,
                            HeaderName = res.fir2.fund.HeaderName,
                            PortfolioCurrency = res.fir2.fund.PortfolioCurrencyId,
                            BaseCurrency = res.fir2.fund.BaseCurrencyId,
                            FileName = res.fir2.fund.FileName,
                            Locked = res.fir2.fund.Locked

                        }).ToList();

这很好用...但是，我想使用以下代码:

and this works fine... However, I would like to use this code:

                var result = _context.Funds
                    .Include(a => a.FundsInReports)
                    .ThenInclude(a => a.Report)         // Many to many , intellisense is not working here !
                    .Select(res => new FundsResponse()
                    {
                        FundId = res.Id,
                        LegalName = res.LegalName,
                        HeaderName = res.HeaderName,
                        PortfolioCurrency = res.PortfolioCurrencyId,
                        BaseCurrency = res.BaseCurrencyId,
                        FileName = res.FileName,
                        Locked = res.Locked

                    }).ToList();

但我不知道如何在此代码中添加过滤(位置).谢谢...

but I don't know how to add filtering (where) into this code. Thanks...

推荐答案

您不要在自定义投影中使用包含".它们是产生结果的替代方法.因此，只需运行

You don't use Include with custom projections. They are alternative approaches to generating results. So just run

var result = _context.FundsInReport
    .Where( fr => fr.ReportId == someId )
    .Select(fr => new FundsResponse()
    {
        FundId = fr.Fund.Id,
        LegalName = fr.Fund.LegalName,
        HeaderName = fr.Fund.HeaderName,
        PortfolioCurrency = fr.Fund.PortfolioCurrencyId,
        BaseCurrency = fr.Fund.BaseCurrencyId,
        FileName = fr.Fund.FileName,
        Locked = fr.Fund.Locked
    }).ToList();

这篇关于EF核心查询通过过滤进行多对多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！