dataobjects-net icon indicating copy to clipboard operation
dataobjects-net copied to clipboard

Wrong query count translation (enum condition in select)

Open letarak opened this issue 11 months ago • 0 comments

DO 7.1.1/7.2.0-Beta-1 Example, see generated sql queries

private static async Task Main(string[] args)
{
    try
    {
        DbHelper.ExecuteNonQuery("DROP DATABASE [DO-Tests]");
    }
    catch (Exception)
    {
    }

    DbHelper.ExecuteNonQuery("CREATE DATABASE [DO-Tests]");

    var currentConnection = new SqlConnectionStringBuilder(DbHelper.ConnectionString());

    var dc = new DomainConfiguration("sqlserver", currentConnection.ToString());

    dc.Types.Register(typeof(SqlLogModule));
    dc.Types.Register(typeof(TestEntity));
    dc.Types.Register(typeof(LinkEntity));

    dc.UpgradeMode = DomainUpgradeMode.Recreate;

    await using var d = await Domain.BuildAsync(dc);

    await using (var s = await d.OpenSessionAsync())
    await using (var t = await s.OpenTransactionAsync())
    {
        _ = new TestEntity(s) { Name = "1", EnumValue = EnumValue.A };
        _ = new TestEntity(s) { Name = "2" };

        t.Complete();
    }

    SqlLogModule.Ready = true;
    
    await using (var s = await d.OpenSessionAsync())
    await using (await s.OpenTransactionAsync())
    {
        var query = s.Query.All<TestEntity>()
            .Select(it => new Poco
            {
                Id = it.Id, 
                Male = it.EnumValue.In(EnumValue.A)
            })
            .Where(it => Query.All<LinkEntity>().Select(e => e.Id).Contains(it.Id));

        // Unhandled exception. NUnit.Framework.AssertionException:   Expected: 1  But was:  0
        Assert.AreEqual(GetCount(query), GetMaterializedCount(query));
    }
}

private static int GetCount(IQueryable<Poco> query)
{
    /* SELECT COUNT_BIG(*) AS [c01umn2]
         FROM (SELECT [a].[Id], 
                        @p0_0 AS [TypeId],  
                        [a].[Name],  
                        [a].[EnumValue],    
                        CAST( (CASE 
                                    WHEN ([a].[EnumValue] IN (@p0_1_0_0)) 
                                        THEN  1 
                                        ELSE  0 
                                    END)  AS bit) AS [#a] 
         FROM  [dbo].[Program.TestEntity] [a]) [b] 
    WHERE  ([b].[#a] =  1); */
    
    return query.Count();
}

private static int GetMaterializedCount(IQueryable<Poco> query)
{
    /* SELECT [a].[Id], [a].[#a]
         FROM (SELECT [b].[Id], 
                        @p0_0 AS [TypeId],  
                        [b].[Name],  
                        [b].[EnumValue],    
                        CAST( (CASE 
                                    WHEN ([b].[EnumValue] IN (@p0_1_0_0)) 
                                        THEN  1 
                                        ELSE  0 
                                    END)  AS bit) AS [#a] 
            FROM  [dbo].[Program.TestEntity] [b]) [a] 
            WHERE  EXISTS (SELECT    *  
                             FROM  [dbo].[Program.LinkEntity] [c]
                             WHERE  ([c].[Id] = [a].[Id]) ); */
    
    return query.ToArray().Length;
}

[HierarchyRoot]
public class TestEntity : Entity
{
    public TestEntity(Session session) : base(session)
    {
    }

    [Key] [Field(Nullable = false)] public int Id { get; set; }

    [Field(Nullable = false)] public string Name { get; set; }
    
    [Field]
    public EnumValue EnumValue { get; set; }
}

[HierarchyRoot]
public class LinkEntity : Entity
{
    public LinkEntity(Session session) : base(session)
    {
    }

    [Key] [Field(Nullable = false)] public int Id { get; set; }

    [Field(Nullable = false)] public string Name { get; set; }
}

public class SqlLogModule : IModule
{
    [ServiceConstructor]
    public SqlLogModule()
    {
    }
    
    public static bool Ready { get; set; }
    
    /// <inheritdoc />
    public void OnBuilt(Domain domain)
    {
        domain.SessionOpen += (_, args) => args.Session.Events.DbCommandExecuting += DbCommandExecuting;
    }

    /// <inheritdoc />
    public void OnDefinitionsBuilt(BuildingContext context, DomainModelDef model)
    {
    }

    private void DbCommandExecuting(object? sender, DbCommandEventArgs e)
    {
        if (Ready)
        {
            Console.WriteLine(e.Command.CommandText);
        }
    }
}

public class Poco
{
    public int Id { get; set; }
    public bool Male { get; set; }
}

public enum EnumValue
{
    A = 1
}

letarak avatar Mar 22 '24 10:03 letarak