使用linq query with include/then#include vs使用sql query with join和getting db server error

我有一个应用程序，我需要能够搜索和显示记录工作；有传呼。
我有一个方法，可以拉取所有记录，并通过分页正确显示它们。正是这种搜索似乎给sql数据库方面的工作带来了麻烦。
我有使用sql的var listquery和使用linq的var listquery2。我更愿意使用linq，但它也给我带来了麻烦，让所有相同的字段填充为sql查询。
我想要达到的是当我做排序时，如果（！isnullorempty（partparams.searchstring））'查看项目列表，获取与之匹配的项目，并将列表传递给pagedlist方法的返回。
我从3个表（part，brand，supplier）中提取，两个键表（partbrand，partsupplier）使用主键字段。当从数据库中提取记录时，我将它们添加到用于显示特定字段（materialpart）的新实体中。var listquery在不使用搜索时工作。所有的记录都会被传回正确的页面。var listquery2不完整，因为我想设置合并实体，但是在关联数据时遇到了问题。
如果需要的话，我可以添加类实体来帮助实现清晰性。

public async Task<PagedList<MaterialPart>> GetMaterialParts(PartParams partParams)
        {
            var listQuery = from p in _dbcontext.Parts
                            join ps in _dbcontext.PartsSuppliers on p.Id equals ps.PartId
                            join s in _dbcontext.Suppliers on ps.SupplierId equals s.Id
                            join pb in _dbcontext.PartsBrands on p.Id equals pb.PartId
                            join b in _dbcontext.Brands on pb.BrandId equals b.Id
                            select (new MaterialPart
                            {
                                PartId = p.Id,
                                PartNumber = p.PartNumber,
                                Description = p.Description,
                                SupplierId = s.Id,
                                SupplierName = s.SupplierName,
                                BrandId = b.Id,
                                BrandName = b.BrandName,
                                Cost = ps.Cost
                            });

            var listQuery2 = _dbcontext.Parts
                    .Include(x => x.PartBrands)
                        .ThenInclude(x => x.Brand)
                    .Include(x => x.PartSuppliers)
                        .ThenInclude(x => x.Supplier)
                    .Select(t => new MaterialPart
                    {
                        PartId = t.Id,
                        PartNumber = t.PartNumber,
                        Description = t.Description
                    });

            if (!string.IsNullOrEmpty(partParams.SearchString))
            {
                listQuery = listQuery
                    .AsQueryable()
                    .Where(x => x.PartNumber
                        .Contains(partParams.SearchString, StringComparison.OrdinalIgnoreCase)
                    || x.Description
                        .Contains(partParams.SearchString, StringComparison.OrdinalIgnoreCase));
            }

            return await PagedList<MaterialPart>.CreateAsync(listQuery,
                partParams.PageNumber,
                partParams.PageSize);

        }

我在postman中的api url如下所示：

http://localhost:5001/api/materials?pageNumber=1&pageSize=5&searchString=100-

sql server错误

System.InvalidOperationException: The LINQ expression 'DbSet<Part>
    .Join(
        outer: DbSet<PartSupplier>, 
        inner: p => p.Id, 
        outerKeySelector: p0 => p0.PartId, 
        innerKeySelector: (p, p0) => new TransparentIdentifier<Part, PartSupplier>(
            Outer = p, 
            Inner = p0
        ))
    .Join(
        outer: DbSet<Supplier>, 
        inner: ti => ti.Inner.SupplierId, 
        outerKeySelector: s => s.Id, 
        innerKeySelector: (ti, s) => new TransparentIdentifier<TransparentIdentifier<Part, PartSupplier>, Supplier>(
            Outer = ti, 
            Inner = s
        ))
    .Join(
        outer: DbSet<PartBrand>, 
        inner: ti0 => ti0.Outer.Outer.Id, 
        outerKeySelector: p1 => p1.PartId, 
        innerKeySelector: (ti0, p1) => new TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<Part, PartSupplier>, Supplier>, PartBrand>(
            Outer = ti0, 
            Inner = p1
        ))
    .Join(
        outer: DbSet<Brand>, 
        inner: ti1 => ti1.Inner.BrandId, 
        outerKeySelector: b => b.Id, 
        innerKeySelector: (ti1, b) => new TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<Part, PartSupplier>, Supplier>, PartBrand>, Brand>(
            Outer = ti1, 
            Inner = b
        ))
    .Where(ti2 => ti2.Outer.Outer.Outer.Outer.PartNumber.Contains(
        value: __partParams_SearchString_0, 
        comparisonType: OrdinalIgnoreCase) || ti2.Outer.Outer.Outer.Outer.Description.Contains(
        value: __partParams_SearchString_0, 
        comparisonType: OrdinalIgnoreCase))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at pMinder4.API.Helpers.PagedList`1.CreateAsync(IQueryable`1 source, Int32 pageNumber, Int32 pageSize) in C:\Users\lsieting\Documents\Source\repos\pMinder4\pMinder4.API\Helpers\PagedList.cs:line 27
   at pMinder4.API.Data.MaterialRepository.GetMaterialParts(PartParams partParams) in C:\Users\lsieting\Documents\Source\repos\pMinder4\pMinder4.API\Data\MaterialRepository.cs:line 95
   at pMinder4.API.Controllers.MaterialsController.GetMaterialParts(PartParams partParams) in C:\Users\lsieting\Documents\Source\repos\pMinder4\pMinder4.API\Controllers\MaterialsController.cs:line 30
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

使用linq query with include/then#include vs使用sql query with join和getting db server error

1条答案

相关问题

热门标签

最新问答