Composite Key Find with LINQ vs. FetchEntity Failure

Posts   
 
    
SnowHammer
User
Posts: 18
Joined: 11-Feb-2019
# Posted on: 22-May-2019 17:49:27   

I have a project using the latest 5.5.0 LLBLGenPro runtime with the Adapter pattern with .NET 4.6.1 and have exhaustively diagnosed this problem over the past two weeks. The symptom is that I was getting 'duplicate key insertion' when I tried to SaveEntity. The composite key is made up of a DateTime field and another entity reference (i.e. a foreign key to a type table). In the Find query, I was also doing a Prefetch on the second part of the composite key and two other type entities (state and status).

I found that using the LinqMetaData based query worked some of the time to find the data already there, which makes the code attempt an update (using existing_e.IsNew = false; linq.AdapterToUse.SaveEntity(existing_e)) instead of an insert. At other times, it did not find the existing record, so tried to insert a new one (using linq.AdapterToUse.SaveEntity(this, true)).

My class is called CompositeStatus and inherits from CompositeStatusEntity, which is generated by LLBLGen.


        public CompositeStatusEntity Find(LinqMetaData linq)
        {
            PrefetchPath2 prefetch_path = new PrefetchPath2(AiEapm.EntityType.CompositeStatusEntity);
            prefetch_path.Add(PrefetchPathStateType);
            prefetch_path.Add(PrefetchPathStatusType);
            if (linq.AdapterToUse.FetchEntity(this))
            {
                return this;
            }
            else return null;
            //return Find(linq, Dt, CompositeEquipment);
        }

        public static CompositeStatusEntity Find(LinqMetaData linq, DateTime dt, CompositeEquipmentEntity compositeEquipment)
        {
            return linq.CompositeStatus
                .WithPath(new PathEdge<CompositeEquipmentEntity>(PrefetchPathCompositeEquipment), new PathEdge<StatusTypeEntity>(PrefetchPathStatusType), new PathEdge<StateTypeEntity>(PrefetchPathStateType))
                .Where(o => o.CompositeEquipment == compositeEquipment &&
                o.Dt == dt).SingleOrDefault();
        }


        public CompositeStatus(DateTime dt, CompositeEquipmentEntity compositeEquipment, StatusTypeEntity statusType, StateTypeEntity stateType)
        {
            Dt = dt;
            CompositeEquipment = compositeEquipment;
            StatusType = statusType;
            StateType = stateType;
        }

        public PersistEnum Persist(LinqMetaData linq, ref StringBuilder logSB, bool printUpdate = false, bool saveChanges = true)
        {
            if (!IsValid)
            {
                logSB.AppendFormat("Invalid: {0}\n", ToString());
                return PersistType = PersistEnum.Invalid;
            }
            CompositeStatusEntity existing_e = Find(linq) as CompositeStatusEntity;
            if (existing_e == null)
            {
                PersistType = PersistEnum.Insert;
            }
            else // Keys match, but other values may have changed
            {
                bool had_update = false;
                if (!GU.NullEquals(existing_e.CompositeEquipment, CompositeEquipment) && existing_e.CompositeEquipmentId != CompositeEquipmentId)
                {
                    if (printUpdate) Console.WriteLine("CompositeStatus: CEID {0,10}  {1,12}  Update {2,18}  Old [{3}]  New [{4}]  DT {5} Status {6} {7} State {8} {9}", CompositeEquipment.Id, CompositeEquipment.Hsc, "CompositeEquipment", existing_e.CompositeEquipmentId, CompositeEquipment, GU.DTStr(existing_e.Dt, 7), existing_e.StatusTypeId, existing_e.StatusType.Name, existing_e.StateTypeId, existing_e.StateType.Name);
                    existing_e.CompositeEquipment = CompositeEquipment;
                    had_update = true;
                }
                if (!GU.NullEquals(existing_e.StatusType, StatusType) && existing_e.StatusTypeId != StatusTypeId)
                {
                    if (printUpdate) Console.WriteLine("CompositeStatus: CEID {0,10}  {1,12}  Update {2,18}  Old [{3}]  New [{4}]  DT {5} Status {6} {7} State {8} {9}", CompositeEquipment.Id, CompositeEquipment.Hsc, "StatusType", existing_e.StatusTypeId, StatusType, GU.DTStr(existing_e.Dt, 7), existing_e.StatusTypeId, existing_e.StatusType.Name, existing_e.StateTypeId, existing_e.StateType.Name);
                    existing_e.StatusType = StatusType;
                    had_update = true;
                }
                if (!GU.NullEquals(existing_e.StateType, StateType) && existing_e.StateTypeId != StateTypeId)
                {
                    if (printUpdate) Console.WriteLine("CompositeStatus: CEID {0,10}  {1,12}  Update {2,18}  Old [{3}]  New [{4}]  DT {5} Status {6} {7} State {8} {9}", CompositeEquipment.Id, CompositeEquipment.Hsc, "StateType", existing_e.StateTypeId, StateType, GU.DTStr(existing_e.Dt, 7), existing_e.StatusTypeId, existing_e.StatusType.Name, existing_e.StateTypeId, existing_e.StateType.Name);
                    existing_e.StateType = StateType;
                    had_update = true;
                }
                PersistType = had_update ? PersistEnum.Update : PersistEnum.NoChange;
            }
            if (saveChanges)
            {
                if (PersistType == PersistEnum.Insert) linq.AdapterToUse.SaveEntity(this, true);
                else if (PersistType == PersistEnum.Update)
                {
                    existing_e.IsNew = false;
                    linq.AdapterToUse.SaveEntity(existing_e);
                }
            }
            return PersistType;
        }

        public bool IsValid
        {
            get { return StatusType != null && StateType != null && CompositeEquipment != null && Dt > DateTime.MinValue; }
        }

        public PersistEnum PersistType = PersistEnum.NoChange;

Notice in the first function, the last line is commented out, because it calls the second function, which fails as I described. Using FetchEntity solved my problem.

I tried many things, including restricting the DateTime in various ways in the find, and that only helped a little. I suspect you need a new integration test that generates a bunch of random DateTimes and an associated type entity. Then, insert them in the database. Then, run it again doing a Find to see if the same records are there and do an update or insert as appropriate. I included other stuff from the class to make it clear what is going on. This is how the code is used:


StringBuilder sb = new StringBuilder();
CompositeStatus composite_status = new CompositeStatus(dt, composite_equipment, status_type, state_type);
composite_status.Persist(linq, ref sb);

This was a huge problem for our team, and it stumped 3 of our developers for a couple weeks.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 23-May-2019 10:39:21   

I think this is caused by the fact that the precision of the 'time' portion in sqlserver is less precise than the one in the 'DateTime' struct in .NET. DateTime in sqlserver stores the time as well, but has a precision of 3.33ms. See: https://stackoverflow.com/questions/2620627/ms-sql-datetime-precision-problem

This causes a problem as some datetime values in-memory look different, but in the DB do not. If you want to store the time with high precision, use DateTime2 as type, but then again, it's not infinitely precise: either use tick count (int64) from the datetime .net struct value, or don't use datetime as a PK value, simply because the values aren't as unique as you think they are.

Frans Bouma | Lead developer LLBLGen Pro
SnowHammer
User
Posts: 18
Joined: 11-Feb-2019
# Posted on: 23-May-2019 17:29:40   

We thought the same thing but eliminated that possibility.

We moved from smalldatetime to datetime2. In the code, we also restrict the time to only be to ms accuracy. We also tried just seconds to completely eliminate that issue. I also recognize that smalldatetime is only accurate to minutes.

dt = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second, dt.Millisecond); // Force to only be to ms

Yeah, I know DateTime is accurate to 100s of ns.

Keep in mind that when you present the EXACT SAME DATA this problem happens -- so even if the conversions and resolution limitations are there, it should do the same thing each time with the same original dt value.

What I don't show in the Find query, but we tried in our efforts, was to explicitly control the where clause to have o.Dt.Year == dt.Year && o.Dt.Month == dt.Month && o.Dt.Day == dt.Day && o.Dt.Hour == dt.Hour && o.Dt.Minute == dt.Minute && o.Dt.Second == dt.Second && o.Dt.Millisecond == dt.Millisecond.

So, on the calling side you control the precision and on the Find side you control the precision to match -- this forces it to do what you expect without higher precision issues. Does not help solve this problem -- just makes it slightly better.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 23-May-2019 23:03:35   

Making it slightly better, means it's a datetime precision issue since it hit better when you narrow it down on filtering to the milliseconds.

Did you try to capture the generated SQL and run it manually against the database and see if it returns the expected results.

SnowHammer
User
Posts: 18
Joined: 11-Feb-2019
# Posted on: 23-May-2019 23:07:23   

Yeah, we thought that too -- a whole week was dedicated to that diagnosis.

However, consider this, regardless of precision, if you have a given DateTime value, insert it in the database, then do a find again on the exact same value, it should return a record -- only the FetchEntity does -- hence our conclusion there is a problem with the LINQ version.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 24-May-2019 08:47:52   

But the linq call and fetchentity end up in the same method doing the same fetch using the same predicate....

Please insert 1 row, then do 2 queries: one using the singleordefault and one with fetchentity. The parameter value passed to the query (you can check with ormprofiler, which is free for you see My Account on our website) is exactly the same (I'd be very surprised if it wasn't)...

Frans Bouma | Lead developer LLBLGen Pro