Select query returns mangled data.

[alexwiese]

Select query returns mangled data.

[alexwiese]

Hi @EelhandsMcCoy

Check out https://github.com/alexwiese/EntityFrameworkCore.OpenEdge#gotchas

Basically EF Core thinks they are all the same record as the "ID" is the same.
You need a unique column to act as the ID. If you don't have one then add RowId as per that link

[EelhandsMcCoy]

Huh. I read that, but these tables have "keys" in that they have an index...but I suppose it's not actually unique. I'll add that to each of my models and see what happens, thank you.

[EelhandsMcCoy]

So I added:
[Key]
[Column("rowid")]
public string Rowid { get; set; }

to the model class but I did not modify the context to include this column. This is the modelbuilder's entity definition:

            modelBuilder.Entity<PromoTransHdr>(entity =>
            {
                entity.HasKey(e => new { e.ContractNo, e.ExpireDate });

                entity.ToTable("promo-trans-hdr");

                entity.Property(e => e.ContractNo)
                    .HasColumnName("contract-no")
                    .HasColumnType("character");

                entity.Property(e => e.ExpireDate)
                    .HasColumnName("expire-date")
                    .HasColumnType("date");

                entity.Property(e => e.BillingCode)
                    .HasColumnName("Billing-code")
                    .HasColumnType("character");

                entity.Property(e => e.Description)
                    .IsRequired()
                    .HasColumnName("description")
                    .HasColumnType("character");

                entity.Property(e => e.OrigAmt)
                    .HasColumnName("orig-amt")
                    .HasDefaultValueSql("0");

                entity.Property(e => e.OrigDate)
                    .HasColumnName("orig_date")
                    .HasColumnType("date");

                entity.Property(e => e.PromoId)
                    .HasColumnName("promo-id")
                    .HasDefaultValueSql("0");

                entity.Property(e => e.PromoTransno)
                    .HasColumnName("promo-transno")
                    .HasDefaultValueSql("0");

                entity.Property(e => e.Restricted)
                    .HasColumnName("restricted")
                    .HasColumnType("logical")
                    .HasDefaultValueSql("no");

                entity.Property(e => e.TransDate)
                    .HasColumnName("trans-date")
                    .HasColumnType("date");

                entity.Property(e => e.UsedAmt)
                    .HasColumnName("used-amt")
                    .HasDefaultValueSql("0");
            });

This did not solve the problem.

{
  "value": [
    {
      "rowid": "0000000000105448618",
      "promoId": 103,
      "transDate": "2015-11-30T00:00:00",
      "description": "WarrantyPayment:1170389",
      "contractNo": "Jeff",
      "expireDate": "2015-12-30T00:00:00",
      "origAmt": 6,
      "usedAmt": 6,
      "promoTransno": 137972,
      "billingCode": "GBB",
      "restricted": false,
      "origDate": null
    },
    {
      "rowid": "0000000000105448618",
      "promoId": 103,
      "transDate": "2015-11-30T00:00:00",
      "description": "WarrantyPayment:1170389",
      "contractNo": "Jeff",
      "expireDate": "2015-12-30T00:00:00",
      "origAmt": 6,
      "usedAmt": 6,
      "promoTransno": 137972,
      "billingCode": "GBB",
      "restricted": false,
      "origDate": null
    },
// Repeat dozens of these.
}

The "key" that EF has selected (ContractNo, ExpireDate) is actually pretty nonsensical. The key should be PromoTransno. That is also an index in the database, but it is the second index and is not marked as 'primary.'

I will try adding rowId to the modelBuilder and if that doesn't work I'll see if I can use the second index instead.

[EelhandsMcCoy]

Adding:

                entity.HasKey(e => e.RowId);

                entity.Property(e => e.RowId)
                    .HasColumnName("rowid");

And commenting out the bad primary index/"key" fixed the problem. Thanks a lot for your help!

[alexwiese]

Great to hear that solved your problem. Yeah if there is no unique, primary index defined then EF really has no way of know which index it should be using. Best bet is to always use rowid to be sure. But make sure you’re sending rowid to/from the API to ensure updates/deletes happen correctly. Otherwise change the DB to give it a unique primary index On 6 Sep 2019, at 11:53 pm, EelhandsMcCoy <[email protected]<mailto:[email protected]>> wrote: Closed #12<#12>. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#12?email_source=notifications&email_token=AAQBQO7BJSFFDDOXVJILGBLQIJRU5A5CNFSM4IUAQK72YY3PNVWWK3TUL52HS4DFWZEXG43VMVCXMZLOORHG65DJMZUWGYLUNFXW5KTDN5WW2ZLOORPWSZGOTPLJP7I#event-2614532093>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AAQBQOZASAZTT766XGYHNMDQIJRU5ANCNFSM4IUAQK7Q>.