I have two tables in SQL InvoiceHeader and InvoiceLines that have a one to many relationship where four columns are used as the foreign key. I have no trouble generating entity objects from the database, and can query InvoiceHeader objects without issue.
When I either include the InvoiceLines in the query or try to query them separately, the resulting SQL generated by EF includes in the select statement the InvoiceHeader table name and each of the key columns as a fieldName (example InvoiceHeader.StoreID is included in the select statement as InvoiceHeaderStoreID).
Scaffold generated objects and context build:
InvoiceHeader
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BackOfficeSQL.Data.Models
{
public partial class InvoiceHeader
{
public InvoiceHeader()
{
InvoiceLines = new HashSet<InvoiceLines>();
}
[Key]
[Column("StoreID")]
public short StoreId { get; set; }
[Key]
[Column("CashRegisterID")]
public short CashRegisterId { get; set; }
[Key]
[Column("ShiftCounterID")]
public short ShiftCounterId { get; set; }
[Key]
[Column("InvoiceID")]
public short InvoiceId { get; set; }
[Column("TransactionTypeID")]
public short TransactionTypeId { get; set; }
[Column("CashierID")]
public short? CashierId { get; set; }
[Column(TypeName = "datetime")]
public DateTime? SetupDate { get; set; }
public bool? Audited { get; set; }
[StringLength(50)]
public string AuditedBy { get; set; }
[Column("VendorID")]
public int? VendorId { get; set; }
[Column("POHeadID")]
public int? PoheadId { get; set; }
[StringLength(50)]
public string VendorInvoice { get; set; }
[Column(TypeName = "datetime")]
public DateTime? InvoiceDate { get; set; }
[Column("upsize_ts")]
public byte[] UpsizeTs { get; set; }
[ForeignKey(nameof(TransactionTypeId))]
[InverseProperty(nameof(TransType.InvoiceHeader))]
public virtual TransType TransactionType { get; set; }
[InverseProperty("InvoiceHeader")]
public virtual InvoiceTruckInfo InvoiceTruckInfo { get; set; }
[InverseProperty("InvoiceHeader")]
public virtual ICollection<InvoiceLines> InvoiceLines { get; set; }
}
}
InvoiceLines
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BackOfficeSQL.Data.Models
{
public partial class InvoiceLines
{
[Key]
[Column("StoreID")]
public short StoreId { get; set; }
[Key]
[Column("CashRegisterID")]
public short CashRegisterId { get; set; }
[Key]
[Column("ShiftCounterID")]
public short ShiftCounterId { get; set; }
[Key]
[Column("InvoiceID")]
public short InvoiceId { get; set; }
[Key]
[Column("InvoiceLineID")]
public short InvoiceLineId { get; set; }
[Key]
[Column("DepartmentTypeID")]
public short DepartmentTypeId { get; set; }
[Key]
public int ItemId { get; set; }
[Column("DepartmentID")]
public short? DepartmentId { get; set; }
[Column("MeterID")]
public short? MeterId { get; set; }
[StringLength(100)]
public string Description { get; set; }
public double Quantity { get; set; }
[Column(TypeName = "money")]
public decimal ExtendedPrice { get; set; }
public bool? Taxable { get; set; }
public double? TaxRate { get; set; }
public short? PriceLevel { get; set; }
public double? AfterQuantity { get; set; }
[Column(TypeName = "money")]
public decimal? UnitCost { get; set; }
[Column("UPCCode")]
[StringLength(15)]
public string Upccode { get; set; }
public short? UpdateOnhand { get; set; }
[Column("TableID")]
public int? TableId { get; set; }
[Column("upsize_ts")]
public byte[] UpsizeTs { get; set; }
[ForeignKey("ShiftCounterId,CashRegisterId,InvoiceId,StoreId")]
[InverseProperty("InvoiceLines")]
public virtual InvoiceHeader InvoiceHeader { get; set; }
}
}
InvoiceHeader DBContext
public virtual DbSet<InvoiceHeader> InvoiceHeader { get; set; }
modelBuilder.Entity<InvoiceHeader>(entity =>
{
entity.HasKey(e => new { e.ShiftCounterId, e.CashRegisterId, e.InvoiceId, e.StoreId })
.HasName("aaaaaInvoiceHeader_PK");
entity.HasIndex(e => e.PoheadId)
.HasName("POHeadID");
entity.HasIndex(e => e.VendorId)
.HasName("VendorID");
entity.HasIndex(e => new { e.InvoiceDate, e.TransactionTypeId })
.HasName("IX_InvoiceHeader_InvoiceDate");
entity.HasIndex(e => new { e.ShiftCounterId, e.CashRegisterId, e.InvoiceId, e.TransactionTypeId, e.StoreId })
.HasName("TransactionTypeID")
.IsUnique();
entity.Property(e => e.ShiftCounterId).HasDefaultValueSql("(0)");
entity.Property(e => e.CashRegisterId).HasDefaultValueSql("(0)");
entity.Property(e => e.InvoiceId).HasDefaultValueSql("(0)");
entity.Property(e => e.StoreId).HasDefaultValueSql("(0)");
entity.Property(e => e.Audited).HasDefaultValueSql("(0)");
entity.Property(e => e.AuditedBy).IsUnicode(false);
entity.Property(e => e.CashierId).HasDefaultValueSql("(0)");
entity.Property(e => e.InvoiceDate).HasDefaultValueSql("(getdate())");
entity.Property(e => e.PoheadId).HasDefaultValueSql("(0)");
entity.Property(e => e.SetupDate).HasDefaultValueSql("(getdate())");
entity.Property(e => e.TransactionTypeId).HasDefaultValueSql("(1)");
entity.Property(e => e.UpsizeTs)
.IsRowVersion()
.IsConcurrencyToken();
entity.Property(e => e.VendorId).HasDefaultValueSql("(0)");
entity.Property(e => e.VendorInvoice).IsUnicode(false);
entity.HasOne(d => d.TransactionType)
.WithMany(p => p.InvoiceHeader)
.HasForeignKey(d => d.TransactionTypeId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_InvoiceHeader_TransType");
});
DbContext
public virtual DbSet<InvoiceLines> InvoiceLines { get; set; }
modelBuilder.Entity<InvoiceLines>(entity =>
{
entity.HasKey(e => new { e.ShiftCounterId, e.CashRegisterId, e.InvoiceId, e.InvoiceLineId, e.DepartmentTypeId, e.StoreId, e.ItemId });
entity.HasIndex(e => e.ItemId)
.HasName("ItemId");
entity.HasIndex(e => e.MeterId)
.HasName("IX_MeterID_ServerID");
entity.HasIndex(e => e.Upccode)
.HasName("UPCCode");
entity.HasIndex(e => e.UpdateOnhand)
.HasName("IX_UpdateOnhand");
entity.HasIndex(e => new { e.ShiftCounterId, e.CashRegisterId, e.InvoiceId, e.StoreId })
.HasName("IX_InvoiceLines");
entity.HasIndex(e => new { e.StoreId, e.ShiftCounterId, e.CashRegisterId, e.InvoiceId, e.InvoiceLineId })
.HasName("UK_InvoiceLines")
.IsUnique();
entity.Property(e => e.ShiftCounterId).HasDefaultValueSql("(0)");
entity.Property(e => e.CashRegisterId).HasDefaultValueSql("(0)");
entity.Property(e => e.InvoiceId).HasDefaultValueSql("(0)");
entity.Property(e => e.InvoiceLineId).HasDefaultValueSql("(0)");
entity.Property(e => e.DepartmentTypeId).HasDefaultValueSql("(0)");
entity.Property(e => e.StoreId).HasDefaultValueSql("(0)");
entity.Property(e => e.ItemId).HasDefaultValueSql("(0)");
entity.Property(e => e.AfterQuantity).HasDefaultValueSql("((-1))");
entity.Property(e => e.DepartmentId).HasDefaultValueSql("(0)");
entity.Property(e => e.Description).IsUnicode(false);
entity.Property(e => e.ExtendedPrice).HasDefaultValueSql("(0)");
entity.Property(e => e.MeterId).HasDefaultValueSql("(0)");
entity.Property(e => e.PriceLevel).HasDefaultValueSql("(0)");
entity.Property(e => e.Quantity).HasDefaultValueSql("(0)");
entity.Property(e => e.TaxRate).HasDefaultValueSql("(0)");
entity.Property(e => e.Taxable).HasDefaultValueSql("(0)");
entity.Property(e => e.UnitCost).HasDefaultValueSql("(0)");
entity.Property(e => e.Upccode).IsUnicode(false);
entity.Property(e => e.UpdateOnhand).HasDefaultValueSql("(0)");
entity.Property(e => e.UpsizeTs)
.IsRowVersion()
.IsConcurrencyToken();
entity.HasOne(d => d.InvoiceHeader)
.WithMany(p => p.InvoiceLines)
.HasForeignKey(d => new { d.ShiftCounterId, d.CashRegisterId, d.InvoiceId, d.StoreId })
.HasConstraintName("FK_InvoiceLines_InvoiceHeader");
});
Testing query of entities:
Invoice = context.InvoiceHeader.Where(x => x.StoreId == 1 && x.CashRegisterId == 54 && x.ShiftCounterId == 1 && x.InvoiceId == 24)
// .Include(l => l.InvoiceLines) //Causes An exception
.Include(t => t.InvoiceTruckInfo)
.FirstOrDefault();
To isolate the problem I was able to query the InvoiceLines entity and use a reflection method to find the EF generated SQL.
var query = context.InvoiceLines.Where(x => x.StoreId == 1 && x.CashRegisterId == 54 && x.ShiftCounterId == 1 && x.InvoiceId == 24);
var sql = query.ToSql();
The resulting SQL statement is:
SELECT [i].[ShiftCounterID],
[i].[CashRegisterID],
[i].[InvoiceID],
[i].[InvoiceLineID],
[i].[DepartmentTypeID],
[i].[StoreID],
[i].[ItemId],
[i].[AfterQuantity],
[i].[DepartmentID],
[i].[Description],
[i].[ExtendedPrice],
[i].[InvoiceHeaderCashRegisterId],
[i].[InvoiceHeaderInvoiceId],
[i].[InvoiceHeaderShiftCounterId],
[i].[InvoiceHeaderStoreId],
[i].[MeterID],
[i].[PriceLevel],
[i].[Quantity],
[i].[TableID],
[i].[TaxRate],
[i].[Taxable],
[i].[UnitCost],
[i].[UPCCode],
[i].[UpdateOnhand],
[i].[upsize_ts]
FROM [InvoiceLines] AS [i]
WHERE ((([i].[StoreID] = CAST(1 AS smallint))
AND ([i].[CashRegisterID] = CAST(54 AS smallint)))
AND ([i].[ShiftCounterID] = CAST(1 AS smallint)))
AND ([i].[InvoiceID] = CAST(24 AS smallint))
The following 4 columns are invalid and if I remove them from the SQL above I'm able to run the SQL query without issue:
[i].[InvoiceHeaderCashRegisterId],
[i].[InvoiceHeaderInvoiceId],
[i].[InvoiceHeaderShiftCounterId],
[i].[InvoiceHeaderStoreId],
I've tried various modifications to the EF utility generated entities even though everything I've looked at example and documentation wise makes the entities and context build up seem correct. I'm not sure why or where this combined Table and field name is coming into play being inserted into the resulting SQL select statement.
When the code is executed to query InvoiceHeader and include the InvoiceLines or the InvoiceLines query executed, a SQL exception is thrown, unsurprisingly:
Invalid column name 'InvoiceHeaderCashRegisterId'.
Invalid column name 'InvoiceHeaderInvoiceId'.
Invalid column name 'InvoiceHeaderShiftCounterId'.
Invalid column name 'InvoiceHeaderStoreId'.
