multiple Include failure

[Keops31]

Hello,

After lots of time of investigation, I could not manage successfully a multiple INCLUDE query to eager load a mother class including its 2 linked classes.

        var query = _context.MotherClass
            .Include(o => o.LinkedClass1)
            .Include(o => o.LinkedClass2);
        var result = query.ToList();

In fact it fails on data mismatch issue on the object of type LinkedClass2. The ORM detect a STRING whereas the Class Property as well as the DB field are both DOUBLE.

Why? It seems it is because the SQL generated is split in 2 parts joined with a UNION ALL and in the FIRST part of the request the field from the LinkedClass2 table are set to (null). Indeed, the engine is unable to detect a field type if in the FIRST part of the UNION ALL, the field value is NULL and NOT CASTED explicitly.

We have: SELECT (null) FROM ... UNION ALL SELECT 123.45 FROM ...

Note that, as a SQL query result, some strange (Chinese?) characters are returned (the table is nevertheless displayed properly while opened MS Access) => data mismatch

In standard EF, we would have rather (working): SELECT CAST(null as Double) FROM ... UNION ALL SELECT 123.45 FROM ...

Note that the reverse query below allows the ORM to detect the proper field type (Double) if the NULL is on the SECOND part SELECT 123.45 FROM ... UNION ALL SELECT (null) FROM ...

I would recommend to force the CAST of any NULL value in JET SQL Generation mechanism.

Mickael

[bubibubi]

Could you please provide a sample? I can't reproduce the issue but I know there is.

About solution, I think that the only way to cast in Jet is using C* for example CDbl but CDbl(null) raises an error. If you know other ways to cast with Jet please tell me, I could not find any working alternative.

Without a cast, Jet interpret the first null as a string then the number in the second record (the stream of bytes that rapresents the number) is converted to string (so the "chinese" characters are the unicode rapresentation of the ieee rapresentation of 123.45).

See also (https://jetentityframeworkprovider.codeplex.com/discussions/647028) (at the bottom of the discussion, the last post) solved in JetDataReader.GetInt32

[Keops31]

I can't share in detail the code but you can reproduce doing so:

• [EDIT : references direction was wrong] create 3 tables in DB: T1, T2, T3 where T2 refers T1 and T3 refers T1 (T1 <=FK= T2, T1 <=FK= T3) [/EDIT]
• add non-string columns (eg: bool, Float, Int...) to T2 and T3
• add some data in the 3 tables
• create/autogenerate corresponding C# entity classes with same fields types (I created them manually)
• create the context class as needed
• launch the query

var query = _context.T1
            .Include(o => o.T2)
            .Include(o => o.T3);
var result = query.ToList();

Note that I updated EntityFramework Nuget whose depends JET NuGet to version 6.2.

I did not find another way to cast NULL using JET. That said, the workaround you highlighted at the bottom of your answer seems to be a good idea.

[bubibubi]

I can't reproduce the error. Please, have a look to this model.

[Table("T175")]
public class T1
{
    public int Id { get; set; }

    public T2 T2 { get; set; }
    public T3 T3 { get; set; }
}

[Table("T275")]
public class T2 : TBase
{
    public virtual List<T1> T1s { get; set; }        
}

[Table("T375")]
public class T3 : TBase
{
    public virtual List<T1> T1s { get; set; }        
}

public class TBase
{
    public int Id { get; set; }

    public decimal? DecimalNumber { get; set; }
    public float? FloatNumber { get; set; }

}

Running this query var query = context.T1s .Include(o => o.T2) .Include(o => o.T3);

var result = query.ToList();

this is the SQL

SELECT 
[Extent1].[Id] AS [Id], 
[Extent2].[Id] AS [Id1], 
[Extent2].[DecimalNumber] AS [DecimalNumber], 
[Extent2].[FloatNumber] AS [FloatNumber], 
[Extent3].[Id] AS [Id2], 
[Extent3].[DecimalNumber] AS [DecimalNumber1], 
[Extent3].[FloatNumber] AS [FloatNumber1]
FROM ( ( [T175] AS [Extent1]
LEFT OUTER JOIN [T275] AS [Extent2] ON ([Extent1].[T2_Id] = [Extent2].[Id]))
LEFT OUTER JOIN [T375] AS [Extent3] ON ([Extent1].[T3_Id] = [Extent3].[Id]))

so, no UNION, only joins.

If you find a way to reproduce the issue please send me the example and I'll check it.

[Keops31]

[EDIT : references direction was wrong] create 3 tables in DB: T1, T2, T3 where T2 refers T1 and T3 refers T1 (T1 <=FK= T2, T1 <=FK= T3) [/EDIT]

[bubibubi]

Ok, now should work. 6.1.5-rc2. I still have some problems with decimals.

[Keops31]

The multiple Include works in 6.1.5-rc2 but while trying on a large DB (1,2 millions rows in table T3) it takes ages and finishes sometimes OK, sometimes with a BufferOverFlow or with the following error:

   at System.Data.Entity.Core.Objects.DataClasses.RelationshipManager.GetRelationshipType(AssociationType csAssociationType)
   at System.Data.Entity.Core.Objects.DataClasses.RelationshipManager.GetRelatedEndInternal(AssociationType csAssociationType, AssociationEndMember csTargetEnd)
   at System.Data.Entity.Core.Objects.EntityEntry.FindRelatedEntityKeysByForeignKeys(Dictionary`2& relatedEntities, Boolean useOriginalValues)
   at System.Data.Entity.Core.Objects.EntityEntry.TakeSnapshotOfForeignKeys()
   at System.Data.Entity.Core.Objects.EntityEntry.TakeSnapshot(Boolean onlySnapshotComplexProperties)
   at System.Data.Entity.Core.Objects.Internal.SnapshotChangeTrackingStrategy.TakeSnapshot(EntityEntry entry)
   at System.Data.Entity.Core.Objects.Internal.EntityWrapper`1.TakeSnapshot(EntityEntry entry)
   at System.Data.Entity.Core.Objects.ObjectStateManager.AddEntry(IEntityWrapper wrappedObject, EntityKey passedKey, EntitySet entitySet, String argumentName, Boolean isAdded)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
   at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Note that the indexes are property set on the tables. Any suggestion?

[bubibubi]

No suggestions... It's a Microsoft Access issue...