[bug]Issue with linq2db in dotnet 7 ef core model with nullable fields

[aloksharma1]

hi i think this is a possible bug, can you check this query:

pageRepository.Query().Where(x => x.IsActive == true && x.SiteId == accountService.GetSiteId() && x.RecordStatus == RecordStatus.Published)
                   .Select(x => new SlugCache
                   {
                       SlugUrl = x.PageSlugUrl,
                       LastRequested = x.LastRequested,
                       DateModified = x.DateModified,
                       PageTypeName = pageTypesRepository.Query().Where(t => t.Id == x.PageTypeId).Select(t => t.PageTypeName).FirstOrDefault(),
                       IsHomePage = x.IsHomePage
                   }).ToList()

but if i add :

pageRepository.Query().Where(x => x.IsActive == true && x.SiteId == accountService.GetSiteId() && x.RecordStatus == RecordStatus.Published)
                   .Select(x => new SlugCache
                   {
                       SlugUrl = x.PageSlugUrl,
                       LastRequested = x.LastRequested,
                       DateModified = x.DateModified,
                       PageTypeName = pageTypesRepository.Query().Where(t => t.Id == x.PageTypeId).Select(t => t.PageTypeName).ToLinqToDB().FirstOrDefault(),
                       IsHomePage = x.IsHomePage
                   }).ToLinqToDB().ToList()

i am getting following error:

Expression 'x.IsActive' is not a Field.
its defined in model as public new bool? IsActive { get; set; } = true;

full stacktrace:

LinqToDB.Linq.LinqException: Expression 'x.IsActive' is not a Field.
   at LinqToDB.Linq.Builder.TableBuilder.TableContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.TableBuilder.TableContext.ConvertToIndex(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.SubQueryContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.ExpressionContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap, ColumnDescriptor columnDescriptor, Boolean isPureExpression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertCompare(IBuildContext context, ExpressionType nodeType, Expression left, Expression right)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertPredicate(IBuildContext context, Expression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildWhere(IBuildContext parent, IBuildContext sequence, LambdaExpression condition, Boolean checkForSubQuery, Boolean enforceHaving)
   at LinqToDB.Linq.Builder.WhereBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.SelectBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]()
   at LinqToDB.Linq.Query`1.CreateQuery(ExpressionTreeOptimizationContext optimizationContext, ParametersContext parametersContext, IDataContext dataContext, Expression expr)
   at LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext, Expression& expr, Boolean& dependsOnParameters)
   at LinqToDB.Linq.ExpressionQuery`1.GetQuery(Expression& expression, Boolean cache, Boolean& dependsOnParameters)
   at LinqToDB.Linq.ExpressionQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at LinqToDB.EntityFrameworkCore.Internal.LinqToDBForEFQueryProvider`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

please check linq2db.efcore version 7.5.0

[sdanyliv]

If you add .ToLinqToDB() in projection? It has no sense.

[aloksharma1]

ok, i removed it from projection but its the same .ToLinqToDB().ToList() the error didnt go away i am upgrading my project to latest dotnet it is working ok in net 5 version.

[sdanyliv]

public new bool? IsActive { get; set; } = true;

How it is mapped to database?

[aloksharma1]

using fluent api of ef core 7.0.10

builder.Property(e => e.IsActive).IsRequired(false).HasDefaultValueSql("1");

the thing is its working ok if i remove ToLinqToDB.

[aloksharma1]

found it, in some place it was overriden by .IsRequired() it worked for ef core but gave error in linqtodb conversion, looks like linq2db is more strict.

[sdanyliv]

Post your model classes. Base class and this one withIsActive. Will check what happened.

[aloksharma1]

Hi, sorry for late reply have been stuck in this project upgrade so hardly getting any time, the problem still persists as i have explained above, here is the model classes:

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

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

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

public abstract class GeneralEntity : BaseEntity, IEquatable<GeneralEntity>
{
    public virtual long SiteId { get; set; }

    public bool Equals([AllowNull] GeneralEntity other)
    {
        return Equals(other);
    }
}

    public class PageProperties : GeneralEntity
    {
        //to use bigger range on created records
        public virtual new Guid Id { get; set; } = Guid.NewGuid();

        //[StringLength(500)]
        //[Required]
        public virtual string? PageName { get; set; }
        //additional properties redacted   

        public virtual RecordStatus RecordStatus { get; set; }        
        public virtual DateTimeOffset LastRequested { get; set; } = DateTimeOffset.UtcNow;
    }
}

public class Pages : PageProperties
{
    public DateTimeOffset? PublishDate { get; set; }
}

my code is divide in many different modules, so there is a lot of abstract classes. Anyways the issue is due to nullable type handling that much i can tell.

[sdanyliv]

Please add BaseEntity

[aloksharma1]

base entity is same as the above base entity just without nulls and extra cluttering

public abstract class BaseEntity : IBaseEntity<Guid>
{
    public virtual Guid Id { get; set; }

    public DateTimeOffset DateCreated { get; set; } = DateTimeOffset.UtcNow;
    public DateTimeOffset DateModified { get; set; } = DateTimeOffset.UtcNow;

    public bool IsActive { get; set; } = true;
    public bool IsModified { get; set; } =false;
}

[aloksharma1]

hi, were you able to find any solution to this issue? i am mostly stuck with union queries that were working previously like this one error it works individually but when I union it with some other table I get this error

Sequence 'value(LinqToDB.EntityFrameworkCore.LinqToDBForEFToolsDataConnection).GetTable().TagQuery("query called for Images") .Where(pi => ((pi.Id == value(LinqToDB.EntityFrameworkCore.LinqToDBForEFToolsDataConnection).GetTable() .TagQuery("query called for BlogPostImages").Where(pim => ((pim.IsActive == Convert(True, Nullable`1)) AndAlso (pim.BlogId == x.Id))).Select(pim => pim.PostImageId).FirstOrDefault()) AndAlso pi.isPublished)) .OrderBy(pi => pi.SortOrder) .Select(pi => new SearchImages() {ImagePath = pi.ImagePath, ImageAltText = pi.ImageAltText, SortOrder = pi.SortOrder, Id = pi.Id}).ToList()' cannot be converted to SQL.

Note: Using AsSplitQuery made it work, i didnt had to do that before as i am simply copying old code from dotnet 5 to 7.

[AntonC9018]

Hi, sorry for late reply have been stuck in this project upgrade so hardly getting any time, the problem still persists as i have explained above, here is the model classes:

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

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

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

public abstract class GeneralEntity : BaseEntity, IEquatable<GeneralEntity>
{
    public virtual long SiteId { get; set; }

    public bool Equals([AllowNull] GeneralEntity other)
    {
        return Equals(other);
    }
}

    public class PageProperties : GeneralEntity
    {
        //to use bigger range on created records
        public virtual new Guid Id { get; set; } = Guid.NewGuid();

        //[StringLength(500)]
        //[Required]
        public virtual string? PageName { get; set; }
        //additional properties redacted   

        public virtual RecordStatus RecordStatus { get; set; }        
        public virtual DateTimeOffset LastRequested { get; set; } = DateTimeOffset.UtcNow;
    }
}

public class Pages : PageProperties
{
    public DateTimeOffset? PublishDate { get; set; }
}

my code is divide in many different modules, so there is a lot of abstract classes. Anyways the issue is due to nullable type handling that much i can tell.

Why are you redefining those properties, and why are they virtual in the first place? Do you realize what new virtual does?

[aloksharma1]

Hi, sorry for late reply have been stuck in this project upgrade so hardly getting any time, the problem still persists as i have explained above, here is the model classes:

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

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

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

public abstract class GeneralEntity : BaseEntity, IEquatable<GeneralEntity>
{
    public virtual long SiteId { get; set; }

    public bool Equals([AllowNull] GeneralEntity other)
    {
        return Equals(other);
    }
}

    public class PageProperties : GeneralEntity
    {
        //to use bigger range on created records
        public virtual new Guid Id { get; set; } = Guid.NewGuid();

        //[StringLength(500)]
        //[Required]
        public virtual string? PageName { get; set; }
        //additional properties redacted   

        public virtual RecordStatus RecordStatus { get; set; }        
        public virtual DateTimeOffset LastRequested { get; set; } = DateTimeOffset.UtcNow;
    }
}

public class Pages : PageProperties
{
    public DateTimeOffset? PublishDate { get; set; }
}

my code is divide in many different modules, so there is a lot of abstract classes. Anyways the issue is due to nullable type handling that much i can tell.

Why are you redefining those properties, and why are they virtual in the first place? Do you realize what new virtual does?

i am using these base classes in other places too, where i have to override these properties (old database structure is not same for all tables some are using int some guid some long, it a pain but it is what it is).

[MaceWindu]

It is hard to say anything when half of code, required to run query is missing. Please reopen if you can provide isolated example with all code parts: query, mappings, entities