My regular SELECT works fine. But when I use a UNION clause, it only works if I am not using the relationship.
I'm getting some exceptions depending on the way I am doing the SELECT
I am using linq2db fluent mapping with SqlServer. Here are the mapped objects.
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", OtherKey = "Id", 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", OtherKey = "Id", 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", OtherKey = "Id", CanBeNull = true });
categoryBuilder.Property(x => x.IsTransient).IsNotColumn();
I have a GenericGet method, where I basically get Product with their relationships and return the IQueryable with some filters. That IQueryable works fine when I am just, based on my filters, return some Products. Usually using FirstOrDefaultAsync(), .ToListAsync(), and .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;
}
But one specific method uses a UNION clause to get various IQueryables and then returns all of them together. The exception occurs in this specific method that uses UNION. I tried some things with different outcomes.
Case 1 - Original Method
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, null, 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
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()"
Case 2 - Construct the object by myself
private IQueryable<Product> GenericMultipleGet(IEnumerable<ProductFilterModel> productFilters, int? maxItems = null)
{
IQueryable<Product> products = null;
foreach (var filter in productFilters)
{
var productListGeneric = GenericGet(null, filter.Search, null, null, null, filter.Category, filter.Brand, 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;
}
Even if I do something like that, I'm getting the same error
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 = new Store { Id = 12, Name = "Test"}
};
Exception
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, 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 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()"
Case 3 - Without any relationships works fine.
var productListGeneric = GenericGet(null, filter.Search, null, null, null, filter.Category, filter.Brand, 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
};
Is this a limitation of the UNION clause with linq2db, or am I doing something wrong?