Using EFCore 6.0.8 in a .net 6 project.
I receive an Exception: Npgsql.PostgreException: '42703' Column c1.Index does not exist when I try to load a hierarchy of classes.
I know I do not have any table named 'c1' and I don't think I have a column 'Index' anywhere in my code.
Note: After verification I had a column "Index" in a class persisted by EF Core.
I found my problem. To find it, I configure the DbContext to trace SQL command. Then I executed the command (the one constructed by EF Core) directly in DBeaver (any SQL tool could do the job).
For query with link to other tables, EF Core use many aliases to make SQL "join" clause (for relations). Those aliases are made of one letter first, but after they are one letter+digits. To discover those aliases, we have to trace the request made by EF Core to the database. In my case (see below) the table "c1" was an alias used to join tables.
This is the start of my OnConfiguring of my DbContext where I setup "Query tracing":
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.EnableSensitiveDataLogging();
// Trace => Most detailed information
optionsBuilder.LogTo(DbLogger.WriteLine, Microsoft.Extensions.Logging.LogLevel.Trace);
...
And my logger:
public class DbLogger
{
public Log Log { get; }
private DbLogger()
{
Log = new Log(null, false, null);
Log.Name = "Ef Core";
Log.IsAutoSaveOnChange = false;
Log.IsAutoSaveOnExit = false;
}
public static DbLogger Instance { get; } = new DbLogger();
public static void WriteLine(string ? msg)
{
Instance.Log.AddEntry(LogLevel.LogMessage, msg);
#if DEBUG
Debug.WriteLine($"Database logging: {msg}");
#endif
}
}
This is the result of tracing EF Core. This is the query where I could have found my bug (the Index added field). I do not add the code of the linq query because it is part of a generic query (this is the link to what I use to build the query: Entity Framework Core 2.0.1 Eager Loading on all nested related entities)
SELECT e."ElectricNetworkId", e."EfficiencyCurveFormula", e."Name", t1."ResourceId", t1."CommConfigId", t1."ElectricNetworkId", t1."Name", t1."GenerationPowerMax", t1."GenerationPowerMin", t1."TimeToCooldown", t1."TimeToStartToFullPower", t1."ActualCapacity", t1."StorageCapacity", t1."MaximumPower", t1."SetPoint", t1."Discriminator", t1."CommConfigId0", t1."CommunicationProtocolType", t1."ResourceId0", t1."Index", t1."IpAddress", t1."Port", t1."Discriminator0", t1."Dnp3PropertyBaseId", t1."CommConfigDnp3Id", t1."Description", t1."Index0", t1."Name0", t1."PropertyName", t1."InputOffset", t1."InputScale", t1."IsPersistent", t1."Offset", t1."OutputControl", t1."Scale", t1."SelectRequired", t1."SmpDeadBand", t1."Unit", t1."UnitMultiplier", t1."InputOffset0", t1."InputScale0", t1."Offset0", t1."OutputControl0", t1."Scale0", t1."SelectRequired0", t1."SmpDeadBand0", t1."Unit0", t1."UnitMultiplier0", t1."Discriminator1"
FROM "ElectricNetwork" AS e
LEFT JOIN (
SELECT r."ResourceId", r."CommConfigId", r."ElectricNetworkId", r."Name", r0."GenerationPowerMax", r0."GenerationPowerMin", r0."TimeToCooldown", r0."TimeToStartToFullPower", r2."ActualCapacity", r2."StorageCapacity", c."MaximumPower", c."SetPoint", CASE
WHEN (s."ResourceId" IS NOT NULL) THEN 'GazTurbineReadOnly'
WHEN (b0."ResourceId" IS NOT NULL) THEN 'Battery'
WHEN (w1."ResourceId" IS NOT NULL) THEN 'WaterHeater'
WHEN (c."ResourceId" IS NOT NULL) THEN 'ControllableLoad'
WHEN (w0."ResourceId" IS NOT NULL) THEN 'WindTurbine'
WHEN (w."ResourceId" IS NOT NULL) THEN 'WaterTurbine'
WHEN (g."ResourceId" IS NOT NULL) THEN 'GazTurbine'
WHEN (b."ResourceId" IS NOT NULL) THEN 'Biomass'
END AS "Discriminator", t."CommConfigId" AS "CommConfigId0", t."CommunicationProtocolType", t."ResourceId" AS "ResourceId0", t."Index", t."IpAddress", t."Port", t."Discriminator" AS "Discriminator0", t0."Dnp3PropertyBaseId", t0."CommConfigDnp3Id", t0."Description", t0."Index" AS "Index0", t0."Name" AS "Name0", t0."PropertyName", t0."InputOffset", t0."InputScale", t0."IsPersistent", t0."Offset", t0."OutputControl", t0."Scale", t0."SelectRequired", t0."SmpDeadBand", t0."Unit", t0."UnitMultiplier", t0."InputOffset0", t0."InputScale0", t0."Offset0", t0."OutputControl0", t0."Scale0", t0."SelectRequired0", t0."SmpDeadBand0", t0."Unit0", t0."UnitMultiplier0", t0."Discriminator" AS "Discriminator1"
FROM "Resource" AS r
LEFT JOIN "ResourceGenerator" AS r0 ON r."ResourceId" = r0."ResourceId"
LEFT JOIN "ResourceStorage" AS r2 ON r."ResourceId" = r2."ResourceId"
LEFT JOIN "Biomass" AS b ON r."ResourceId" = b."ResourceId"
LEFT JOIN "GazTurbine" AS g ON r."ResourceId" = g."ResourceId"
LEFT JOIN "WaterTurbine" AS w ON r."ResourceId" = w."ResourceId"
LEFT JOIN "WindTurbine" AS w0 ON r."ResourceId" = w0."ResourceId"
LEFT JOIN "ControllableLoad" AS c ON r."ResourceId" = c."ResourceId"
LEFT JOIN "WaterHeater" AS w1 ON r."ResourceId" = w1."ResourceId"
LEFT JOIN "Battery" AS b0 ON r."ResourceId" = b0."ResourceId"
LEFT JOIN "StorageTestReadOnly" AS s ON r."ResourceId" = s."ResourceId"
LEFT JOIN (
SELECT c0."CommConfigId", c0."CommunicationProtocolType", c0."ResourceId", c1."Index", c1."IpAddress", c1."Port", CASE
WHEN (c1."CommConfigId" IS NOT NULL) THEN 'CommConfigDnp3'
END AS "Discriminator"
FROM "CommConfig" AS c0
LEFT JOIN "CommConfigDnp3" AS c1 ON c0."CommConfigId" = c1."CommConfigId"
) AS t ON r."ResourceId" = t."ResourceId"
LEFT JOIN (
SELECT d."Dnp3PropertyBaseId", d."CommConfigDnp3Id", d."Description", d."Index", d."Name", d."PropertyName", d0."InputOffset", d0."InputScale", d0."IsPersistent", d0."Offset", d0."OutputControl", d0."Scale", d0."SelectRequired", d0."SmpDeadBand", d0."Unit", d0."UnitMultiplier", d2."InputOffset" AS "InputOffset0", d2."InputScale" AS "InputScale0", d2."Offset" AS "Offset0", d2."OutputControl" AS "OutputControl0", d2."Scale" AS "Scale0", d2."SelectRequired" AS "SelectRequired0", d2."SmpDeadBand" AS "SmpDeadBand0", d2."Unit" AS "Unit0", d2."UnitMultiplier" AS "UnitMultiplier0", CASE
WHEN (d3."Dnp3PropertyBaseId" IS NOT NULL) THEN 'Dnp3OutputBinary'
WHEN (d2."Dnp3PropertyBaseId" IS NOT NULL) THEN 'Dnp3OutputAnalog'
WHEN (d1."Dnp3PropertyBaseId" IS NOT NULL) THEN 'Dnp3InputBinary'
WHEN (d0."Dnp3PropertyBaseId" IS NOT NULL) THEN 'Dnp3InputAnalog'
END AS "Discriminator"
FROM "Dnp3PropertyBase" AS d
LEFT JOIN "Dnp3InputAnalog" AS d0 ON d."Dnp3PropertyBaseId" = d0."Dnp3PropertyBaseId"
LEFT JOIN "Dnp3InputBinary" AS d1 ON d."Dnp3PropertyBaseId" = d1."Dnp3PropertyBaseId"
LEFT JOIN "Dnp3OutputAnalog" AS d2 ON d."Dnp3PropertyBaseId" = d2."Dnp3PropertyBaseId"
LEFT JOIN "Dnp3OutputBinary" AS d3 ON d."Dnp3PropertyBaseId" = d3."Dnp3PropertyBaseId"
) AS t0 ON t."CommConfigId" = t0."CommConfigDnp3Id"
) AS t1 ON e."ElectricNetworkId" = t1."ElectricNetworkId"
ORDER BY e."ElectricNetworkId", t1."ResourceId", t1."CommConfigId0"
