Working with Unions on Different Tables?

[aloksharma1]

Hello, In your examples i saw nothing related to different table union results, i cam here through this StackOverflow Suggestion of yours

https://stackoverflow.com/questions/64247176/ef-core-5-0-union-linq-query-with-sub-selects-not-working

my uqery needs are simple, i just need to query from different tables and show the result with minimal subselect mapping (unlike the above question), i can do this if i make the first query AsEnumerable but that just kills the purpose of doing ops in sql side, here is my minimal code (with repository pattern):

var repository = ServiceProvider.GetService<IRepository<Pages, MyDbContext>>();
            var query = repository.Query().Where(x => 
             x.IsActive == true && x.PageName.StartsWith(SearchTerm) && x.RecordStatus == RecordStatus.Published
            && x.SearchStatus != SearchStatus.DisabledSearchable).Select(x => new SearchResults
            {
                DateCreated = x.DateCreated,
                Id = "" + x.Id,
                Slug = x.PageSlugUrl,
                Title = x.PageName,
                Summary = x.PageSummary
            });
            var repository1 = ServiceProvider.GetService<IRepository<Products, MyDbContext>>();
            var query1 = repository1.Query().Where(x => x.IsActive == true && x.PageName.StartsWith(SearchTerm) && x.RecordStatus == RecordStatus.Published
            && x.SearchStatus != SearchStatus.DisabledSearchable)
                .Select(x => new SearchResults
                {
                    DateCreated = x.DateCreated,
                    Id = "" + x.Id,
                    Slug = x.PageSlugUrl + $"({x.SKU})",
                    Title = x.PageName,
                    Summary = x.PageSummary
                });
            var finalQuery = query.Union(query1);

In EF 5 this gives me an error to 'Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.' .

thanks for the help

[sdanyliv]

Well, have you tried?

finalQuery = finalQuery.ToLinqToDB();

[aloksharma1]

@sdanyliv i just tried your solution, it ended up in Exception "Ambiguous match found." do you want me to attach stacktrace?

[sdanyliv]

Yes, please include stack trace.

[aloksharma1]

here is the stacktrace:

var finalQuery = query.Union(query1).ToLinqToDB();
var result = finalQuery.ToList(); <--- this causes exception

AmbiguousMatchException: Ambiguous match found.
System.RuntimeType.GetPropertyImpl(string name, BindingFlags bindingAttr, Binder binder, Type returnType, Type[] types, ParameterModifier[] modifiers)
System.Type.GetProperty(string name, BindingFlags bindingAttr)
System.Type.GetProperty(string name)
LinqToDB.Extensions.ReflectionExtensions.GetMemberEx(Type type, MemberInfo memberInfo)
LinqToDB.EntityFrameworkCore.EFCoreMetadataReader.GetDbFunctionFromProperty(Type type, PropertyInfo propInfo)
LinqToDB.EntityFrameworkCore.EFCoreMetadataReader.GetAttributes<T>(Type type, MemberInfo memberInfo, bool inherit)
LinqToDB.Mapping.MappingSchema.GetAttributes<T>(Type type, MemberInfo memberInfo, bool inherit)
LinqToDB.Mapping.MappingSchema.GetAttributes<T>(Type type, MemberInfo memberInfo, Func<T, string> configGetter, bool inherit, bool exactForConfiguration)
LinqToDB.Mapping.MappingSchema.GetAttribute<T>(Type type, MemberInfo memberInfo, Func<T, string> configGetter, bool inherit)
LinqToDB.Linq.Builder.ExpressionTreeOptimizationContext.GetExpressionAttribute(MemberInfo member)
LinqToDB.Linq.Builder.ExpressionTreeOptimizationContext.IsServerSideOnly(Expression expr)
LinqToDB.Linq.Builder.ExpressionTreeOptimizationContext.CanBeCompiledFind(CanBeCompiledContext context, Expression ex)
LinqToDB.Linq.Builder.ExpressionTreeOptimizationContext+<>c.<GetCanBeCompiledVisitor>b__31_0(CanBeCompiledContext ctx, Expression e)
LinqToDB.Expressions.FindVisitor<TContext>.Find(Expression expr)
LinqToDB.Linq.Builder.ExpressionTreeOptimizationContext.CanBeCompiled(Expression expr)
LinqToDB.Linq.Builder.ExpressionTreeOptimizationContext.ExposeExpressionTransformer(Expression expr)
LinqToDB.Linq.Builder.ExpressionTreeOptimizationContext+<>c.<ExposeExpression>b__44_0(ExpressionTreeOptimizationContext ctx, Expression e)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform(Expression expr)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform(Expression expr)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform(Expression expr)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.TransformMemberBinding(MemberBinding b)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform<T>(IList<T> source, Func<T, T> func)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform(Expression expr)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform(Expression expr)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform(Expression expr)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform<T>(IList<T> source)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform(Expression expr)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform<T>(IList<T> source)
LinqToDB.Expressions.TransformInfoVisitor<TContext>.Transform(Expression expr)
LinqToDB.Linq.Builder.ExpressionTreeOptimizationContext.ExposeExpression(Expression expression)
LinqToDB.Linq.Builder.ExpressionBuilder.ExposeExpression(Expression expression)
LinqToDB.Linq.Builder.ExpressionBuilder.OptimizeExpression(Expression expression)
LinqToDB.Linq.Builder.ExpressionBuilder.ConvertExpressionTree(Expression expression)
LinqToDB.Linq.Builder.ExpressionBuilder..ctor(Query query, ExpressionTreeOptimizationContext optimizationContext, IDataContext dataContext, Expression expression, ParameterExpression[] compiledParameters)
LinqToDB.Linq.Query<T>.CreateQuery(ExpressionTreeOptimizationContext optimizationContext, IDataContext dataContext, Expression expr)
LinqToDB.Linq.Query<T>.GetQuery(IDataContext dataContext, ref Expression expr)
LinqToDB.Linq.ExpressionQuery<T>.GetQuery(ref Expression expression, bool cache)
LinqToDB.Linq.ExpressionQuery<T>.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
LinqToDB.EntityFrameworkCore.Internal.LinqToDBForEFQueryProvider<T>.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
System.Collections.Generic.List<T>..ctor(IEnumerable<T> collection)
System.Linq.Enumerable.ToList<TSource>(IEnumerable<TSource> source)
Ultra.WebHost.Controllers.TestController.Search(string SearchTerm, Nullable<int> num, Nullable<int> Size) in TestController.cs

Source: System.Private.CoreLib

InnerException Null

[sdanyliv]

Looks like a bug. Will try to figure out why it is failed ASAP.

[sdanyliv]

Please confirm, looks like you have some property in your classes which is marked as new keyword?

[aloksharma1]

Hi, Yes on the table classes as this is a code first project. I have different type of ids for different tables so overriding them with new keyword. But the subselect class "SearchResults" is fixed poco class with no new keyword. Any workarounds, the project is already done & have lots of tables with new Id cant change it at this point.

thanks for your help

[sdanyliv]

No, just supply me with these classes, I have to reproduce. Then I'll prepare fix today.

[aloksharma1]

Here is the Base Class:

public abstract class BaseEntity : IEquatable<BaseEntity>, IBaseEntity<long>
    {
        [Key]
        public virtual long Id { get; set; }  

        public bool? IsActive { get; set; } = true;
        public DateTimeOffset? DateCreated { get; set; } = DateTimeOffset.UtcNow;
        public DateTimeOffset? DateModified { get; set; } = DateTimeOffset.UtcNow;

        [NotMapped]
        public virtual Constants.ActionType? ActionType { get; set; } = Constants.ActionType.Create;
        public bool Equals([AllowNull] BaseEntity other)
        {
            return Equals(other);
        }
    }

you can ignore the interface, basically id is virtual so i can change the type as per my table requirement & every table inherits from BaseClass to generate common columns. for e.g.

public class Category : BaseEntity
    {       
        public new Guid Id { get; set; } = Guid.NewGuid();
        public string CategoryName { get; set; }
        public Guid? CategoryParentId { get; set; }
    } 

hope this is helpful, also i am using Entity version 5.0.9 Linqtodb.EfCore 5.0.6

[sdanyliv]

Release 5.7.0 is ready. Issue should be fixed.

[aloksharma1]

hello, i updated the package to 5.7.0 but the ambiguous exception still coming up for me. I can upload a test github project if you want. thanks for the help

[sdanyliv]

Yes, sure. Simple sample. will be. enough. I have analysed code and have not found any similar potential places.

[aloksharma1]

Hello,

here is the base miminum sample with ef/repository & multi level base classes git repo also i cloned your repo & did a analysis on the code part you pointed out. turns out this method is giving null in case of guid Id:

var found = _calculatedExtensions.GetOrAdd(propInfo, mi =>
            {
                --->//Debugger not hitting here
                EFCoreExpressionAttribute? result = null;

                if ((propInfo.GetMethod?.IsStatic != true)
                    && !(mi is DynamicColumnInfo)
                    && !mi.GetCustomAttributes<Sql.ExpressionAttribute>().Any())
                {
                    var objExpr = new SqlTransparentExpression(Expression.Constant(DefaultValue.GetValue(type), type), _mappingSource?.FindMapping(propInfo));

                    var newExpression = _dependencies.MemberTranslatorProvider.Translate(objExpr, propInfo, propInfo.GetMemberType(), _logger!);
                    if (newExpression != null)
                    {
                        var parametersArray = new Expression[] { objExpr };
                        result = ConvertToExpressionAttribute(propInfo, newExpression, parametersArray);
                    }
                }

                return result;
            });

Also i think you can write this part as:

private static PropertyInfo GetPropertyInfoForType(Type type, PropertyInfo propInfo)
        {
            //if (propInfo.DeclaringType == type)
            //  return propInfo;

            var found = type.GetProperties()
                .FirstOrDefault(x => x.Module == propInfo.Module && x.MetadataToken == propInfo.MetadataToken && x.DeclaringType==type);

            return found;// ?? propInfo;
        } 

awaiting your reply

[sdanyliv]

Thanks for the sample project. Problem in the main library linq2db and I have to fix that. Will see, maybe it will be released this weekend.

[aloksharma1]

Hello, i see that its marked as fixed, can you tell me what updates i need to get this working?

[sdanyliv]

Not released yet. Few days.

[aloksharma1]

ok , thanks for the quick reply i will check back by weekend then.

thanks again