我的常规SELECT正常工作。但是，当我使用UNION子句时，仅当我不使用该关系时它才起作用。
根据我执行SELECT的方式，我会遇到一些例外情况
我在SqlServer中使用linq2db流畅映射。这是映射的对象。
var mappingBuilder = dataConnection.MappingSchema.GetFluentMappingBuilder();
//Product
EntityMappingBuilder<Product> productBuilder = mappingBuilder.Entity<Product>();
productBuilder.Property(x => x.Id).HasColumnName("Id").IsPrimaryKey().IsIdentity();
productBuilder.Property(x => x.Name).HasColumnName("Name");
productBuilder.Property(x => x.Category).HasAttribute(new AssociationAttribute { ThisKey = "IdCategory",OtherKey = "Id",CanBeNull = true });
productBuilder.Property(x => x.Store).HasAttribute(new AssociationAttribute { ThisKey = "IdStore",CanBeNull = true });
productBuilder.Property(x => x.MainImageReference).HasColumnName("MainImageReference");
productBuilder.Property(x => x.Price).HasColumnName("Price");
productBuilder.Property(x => x.Brand).HasColumnName("Brand");
productBuilder.Property(x => x.Description).HasColumnName("Description");
productBuilder.Property(x => x.active).HasColumnName("active");
productBuilder.Property(x => x.IsTransient).IsnotColumn();
//Store
EntityMappingBuilder<Store> storeBuilder = mappingBuilder.Entity<Store>();
storeBuilder.Property(x => x.Id).HasColumnName("Id").IsPrimaryKey().IsIdentity();
storeBuilder.Property(x => x.Address).HasAttribute(new AssociationAttribute { ThisKey = "IdAddress",CanBeNull = true });
storeBuilder.Property(x => x.IsTransient).IsnotColumn();
//Category
EntityMappingBuilder<Category> categoryBuilder = mappingBuilder.Entity<Category>();
categoryBuilder.Property(x => x.Id).HasColumnName("Id").IsPrimaryKey().IsIdentity();
categoryBuilder.Property(x => x.Name).HasColumnName("Name");
categoryBuilder.Property(x => x.Description).HasColumnName("Description");
categoryBuilder.Property(x => x.Level).HasColumnName("Level");
categoryBuilder.Property(x => x.IdParent).HasColumnName("Parent");
categoryBuilder.Property(x => x.ParentCategory).HasAttribute(new AssociationAttribute { ThisKey = "IdParent",CanBeNull = true });
categoryBuilder.Property(x => x.IsTransient).IsnotColumn();
我有一个GenericGet方法，在这里我基本上得到带有它们关系的Product并返回带有一些过滤器的IQueryable。当我只是基于过滤器返回一些产品时，该IQueryable可以正常工作。通常使用FirstOrDefaultAsync（）、. ToListAsync（）和.CountAsync（）。
private IQueryable<Product> GenericGet(parameters)
{
var products = from p in _dataConnection.getconnection().GetTable<Product>()
.LoadWith(x => x.Category)
.LoadWith(x => x.Category.ParentCategory)
.LoadWith(x => x.Category.ParentCategory.ParentCategory)
.LoadWith(x => x.Category.ParentCategory.ParentCategory.ParentCategory)
.LoadWith(x => x.Category.ParentCategory.ParentCategory.ParentCategory.ParentCategory)
.LoadWith(x =>x.Category.ParentCategory.ParentCategory.ParentCategory.ParentCategory.ParentCategory)
.LoadWith(x => x.Category.ParentCategory.ParentCategory.ParentCategory.ParentCategory.ParentCategory.ParentCategory)
.LoadWith(x => x.Category.ParentCategory.ParentCategory.ParentCategory.ParentCategory.ParentCategory.ParentCategory.ParentCategory)
.LoadWith(s => s.Store)
select p;
// Filters
// various checks only in products,based on the parameters
if (id != null && id != 0)
{
products = from p in products
where p.Id == id
select p;
}
.
.
.
return products;
}
但是一种特定的方法使用UNION子句来获取各种IQueryable，然后将它们全部一起返回。 在使用UNION的此特定方法中会发生异常。 我尝试了一些结果不同的事情。
案例1-原始方法
private IQueryable<Product> GenericMultipleGet(IEnumerable<ProductFilterModel> productFilters,int? maxItems = null)
{
IQueryable<Product> products = null;
foreach (var filter in productFilters)
{
var productList = GenericGet(null,filter.Search,null,filter.Category,filter.Brand,VisibilityType.active);
if (maxItems != null)
{
productList = productList.Take(maxItems.Value);
}
if (products == null)
{
products = productList;
}
else
{
// Exception Here
products = products.Union(productList);
}
}
return products;
}
例外
Exception message: "Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')"
Stack trace: " at System.ThrowHelper.ThrowArgumentOutOfRange_IndexException()\r\n at LinqToDB.SqlQuery.SqlStatement.<>c__DisplayClass35_0.<PrepareQueryAndAliases>b__0(IQueryElement expr)\r\n at LinqToDB.SqlQuery.QueryVisitor.Visit2(IQueryElement element)\r\n at LinqToDB.SqlQuery.QueryVisitor.Visit2X(SelectQuery q)\r\n at LinqToDB.SqlQuery.QueryVisitor.Visit2(IQueryElement element)\r\n at LinqToDB.SqlQuery.QueryVisitor.Visit2(IQueryElement element)\r\n at LinqToDB.SqlQuery.QueryVisitor.VisitAll(IQueryElement element,action`1 action)\r\n at LinqToDB.SqlQuery.SqlStatement.PrepareQueryAndAliases()\r\n at LinqToDB.Linq.QueryRunner.FinalizeQuery(Query query)\r\n at LinqToDB.Linq.QueryRunner.GetExecuteQuery[T](Query query,Func`8 queryFunc)\r\n at LinqToDB.Linq.QueryRunner.SetRunQuery[T](Query`1 query,Expression`1 expression)\r\n at LinqToDB.Linq.QueryRunner.SetRunQuery[T](Query`1 query,Expression`1 expression)\r\n at LinqToDB.Linq.Builder.SetOperationBuilder.Setoperationcontext.BuildQuery[T](Query`1 query,ParameterExpression queryParameter)\r\n at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]()\r\n at LinqToDB.Linq.Query`1.CreateQuery(IDataContext dataContext,Expression expr)\r\n at LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext,Expression& expr)\r\n at LinqToDB.Linq.ExpressionQuery`1.GetQuery(Expression& expression,Boolean cache)\r\n at LinqToDB.Linq.ExpressionQuery`1.<GetForEachAsync>d__19.MoveNext()\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.ConfiguredTaskawaitable.ConfiguredTaskawaiter.GetResult()\r\n at LinqToDB.AsyncExtensions.<ToListAsync>d__8`1.MoveNext()\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter`1.GetResult()\r\n at StoreAggregator.Api.Data.Repository.ProductRepository.<GetMultiple>d__11.MoveNext() in E:\\Projetos\\Store Aggregator\\store-aggregator-api\\StoreAggregatorApi\\StoreAggregator.Api.Repository\\Repository\\ProductRepository.cs:line 228\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter`1.GetResult()\r\n at StoreAggregator.Api.Services.Products.SimilarProduct.<GetSimiliar>d__4.MoveNext() in E:\\Projetos\\Store Aggregator\\store-aggregator-api\\StoreAggregatorApi\\StoreAggregator.Api.Services\\Products\\SimilarProduct.cs:line 26\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter`1.GetResult()\r\n at StoreAggregator.Api.Controllers.ProductController.<GetSimilarProduct>d__7.MoveNext() in E:\\Projetos\\Store Aggregator\\store-aggregator-api\\StoreAggregatorApi\\StoreAggregator.Api\\Controllers\\ProductController.cs:line 67\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()\r\n at microsoft.AspNetCore.Mvc.Infrastructure.actionmethodExecutor.TaskOfactionResultExecutor.<Execute>d__0.MoveNext()\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Threading.Tasks.Valuetask`1.get_Result()\r\n at System.Runtime.CompilerServices.Valuetaskawaiter`1.GetResult()\r\n at microsoft.AspNetCore.Mvc.Infrastructure.ControlleractionInvoker.<<InvokeactionmethodAsync>g__Awaited|12_0>d.MoveNext()\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at microsoft.AspNetCore.Mvc.Infrastructure.ControlleractionInvoker.<<InvokeNextactionFilterAsync>g__Awaited|10_0>d.MoveNext()"
案例2-自己构造对象
private IQueryable<Product> GenericMultipleGet(IEnumerable<ProductFilterModel> productFilters,int? maxItems = null)
{
IQueryable<Product> products = null;
foreach (var filter in productFilters)
{
var productListGeneric = GenericGet(null,VisibilityType.active);
var productList = from p in productListGeneric
select new Product
{
active = p.active,Brand = p.Brand,CreationDate = p.CreationDate,Description = p.Description,Id = p.Id,IdCategory = p.IdCategory,IdStore = p.IdStore,LastModification = p.LastModification,MainImageReference = p.MainImageReference,Price = p.Price,Name = p.Name,// The Exception occur if I included this line
Store = p.Store
};
if (maxItems != null)
{
productList = productList.Take(maxItems.Value);
}
if (products == null)
{
products = productList;
}
else
{
// Exception: "Types in Union are constructed incompatibly."
products = products.Union(productList);
}
}
return products;
}
即使我做了类似的事情，我也会遇到相同的错误
var productList = from p in productListGeneric
select new Product
{
active = p.active,// The Exception occur if I included this line
Store = new Store { Id = 12,Name = "Test"}
};
例外
Exception message: "Types in Union are constructed incompatibly."
Stack trace: " at LinqToDB.Linq.Builder.SetOperationBuilder.Setoperationcontext.Init()\r\n at LinqToDB.Linq.Builder.SetOperationBuilder.Setoperationcontext..ctor(SubQueryContext sequence1,SubQueryContext sequence2,MethodCallExpression methodCall)\r\n at LinqToDB.Linq.Builder.SetOperationBuilder.BuildMethodCall(ExpressionBuilder builder,MethodCallExpression methodCall,BuildInfo buildInfo)\r\n at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder,BuildInfo buildInfo)\r\n at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)\r\n at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]()\r\n at LinqToDB.Linq.Query`1.CreateQuery(IDataContext dataContext,Boolean cache)\r\n at LinqToDB.Linq.ExpressionQuery`1.<GetForEachAsync>d__19.MoveNext()\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.ConfiguredTaskawaitable.ConfiguredTaskawaiter.GetResult()\r\n at LinqToDB.AsyncExtensions.<ToListAsync>d__8`1.MoveNext()\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter`1.GetResult()\r\n at StoreAggregator.Api.Data.Repository.ProductRepository.<GetMultiple>d__11.MoveNext() in E:\\Projetos\\Store Aggregator\\store-aggregator-api\\StoreAggregatorApi\\StoreAggregator.Api.Repository\\Repository\\ProductRepository.cs:line 247\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter`1.GetResult()\r\n at StoreAggregator.Api.Services.Products.SimilarProduct.<GetSimiliar>d__4.MoveNext() in E:\\Projetos\\Store Aggregator\\store-aggregator-api\\StoreAggregatorApi\\StoreAggregator.Api.Services\\Products\\SimilarProduct.cs:line 26\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter`1.GetResult()\r\n at StoreAggregator.Api.Controllers.ProductController.<GetSimilarProduct>d__7.MoveNext() in E:\\Projetos\\Store Aggregator\\store-aggregator-api\\StoreAggregatorApi\\StoreAggregator.Api\\Controllers\\ProductController.cs:line 67\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()\r\n at microsoft.AspNetCore.Mvc.Infrastructure.actionmethodExecutor.TaskOfactionResultExecutor.<Execute>d__0.MoveNext()\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Threading.Tasks.Valuetask`1.get_Result()\r\n at System.Runtime.CompilerServices.Valuetaskawaiter`1.GetResult()\r\n at microsoft.AspNetCore.Mvc.Infrastructure.ControlleractionInvoker.<<InvokeactionmethodAsync>g__Awaited|12_0>d.MoveNext()\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.Taskawaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.Taskawaiter.GetResult()\r\n at microsoft.AspNetCore.Mvc.Infrastructure.ControlleractionInvoker.<<InvokeNextactionFilterAsync>g__Awaited|10_0>d.MoveNext()"
情况3-没有任何关系，效果很好。
var productListGeneric = GenericGet(null,VisibilityType.active);
var productList = from p in productListGeneric
select new Product
{
active = p.active,Name = p.Name
};
这是linq2db的UNION子句的限制吗，还是我做错了什么？