Efcore: SqliteException 'no such column: c.state_id'
After upgrading ASP.NET Core 1.1 project to 2.1.4, I am no longer able to execute custom queries I wrote. Please see below stack trace and code. I don't think so its an issue with the code as it worked just fine when it was running in v1.1. Anyone have any workaround in the mean time? I would hate to downgrade my project back to 1.1.
Exception message:
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: c.state_id'.
Stack trace:
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: c.state_id'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
Microsoft.EntityFrameworkCore.Query:Error: An exception occurred in the database while iterating the results of a query for context type 'Sunbelt.Data.ApplicationDbContext'.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: c.state_id'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
Steps to reproduce
Include a complete code listing (or project/solution) that we can run to reproduce the issue.
This query use to work fine in EF Core 1.0 version. After upgrading to 2.1 it is no longer working.
# region
var results = from c in _context.Customers
join BState in _context.States on c.StateId equals BState.Id into lBStates
from lBState in lBStates.Where(r => r.Id == c.StateId).DefaultIfEmpty()
join MState in _context.States on c.MStateId equals MState.Id into LMStates
from LMState in LMStates.Where(r => r.Id == c.MStateId).DefaultIfEmpty()
join status in _context.Statuses on c.StatusId equals status.Id into lstatuses
from lstatus in lstatuses.Where(r => r.Id == c.StatusId).DefaultIfEmpty()
join u in _context.Employees on c.ZoneManager equals u.Id into lus
from lu in lus.Where(r => r.Id == c.ZoneManager).DefaultIfEmpty()
join regions in _context.Regions on c.RegionId equals regions.Id into lregions
from lregion in lregions.Where(r => r.Id == c.Id).DefaultIfEmpty()
join zone in _context.Zones on c.ZoneId equals zone.Id into lzones
from lzone in lzones.Where(r => r.Id == c.ZoneId).DefaultIfEmpty()
join storetype in _context.StoreTypes on c.StoreTypeId equals storetype.Id into lstoretypes
from lstoretype in lstoretypes.Where(r => r.Id == c.StoreTypeId).DefaultIfEmpty()
join gasbrand in _context.GasBrands on c.GasBrandId equals gasbrand.Id into lgasbrands
from lgasbrand in lgasbrands.Where(r => r.Id == c.GasBrandId).DefaultIfEmpty()
join propanebrand in _context.PropaneBrands on c.PropaneBrandId equals propanebrand.Id into lpropandbrands
from lpropanebrand in lpropandbrands.Where(r => r.Id == c.PropaneBrandId).DefaultIfEmpty()
join wasteservice in _context.WasteServices on c.WasteServiceId equals wasteservice.Id into lwasteservices
from lwasteservice in lwasteservices.Where(r => r.Id == c.WasteServiceId).DefaultIfEmpty()
join donationbox in _context.YesNos on c.DonationBoxId equals donationbox.Id into ldonationboxes
from ldonationbox in ldonationboxes.Where(r => r.Id == c.DonationBoxId).DefaultIfEmpty()
join pumptoppersize in _context.PumpTopperSizes on c.PumpTopperSizeId equals pumptoppersize.Id into lpumptoppersizes
from lpumptopper in lpumptoppersizes.Where(r => r.Id == c.PumpTopperSizeId).DefaultIfEmpty()
join fountainbrand in _context.FountainMachines on c.FountainMachineId equals fountainbrand.Id into lfountainbrands
from lfountainbrand in lfountainbrands.Where(r => r.Id == c.FountainMachineId).DefaultIfEmpty()
join coffee in _context.Coffees on c.CoffeeId equals coffee.Id into lcoffees
from lcoffee in lcoffees.Where(r => r.Id == c.CoffeeId).DefaultIfEmpty()
join slushy in _context.Slushies on c.SlushyId equals slushy.Id into lslushies
from lslushy in lslushies.Where(r => r.Id == c.SlushyId).DefaultIfEmpty()
join milk in _context.Milks on c.MilkId equals milk.Id into lmilks
from lmilk in lmilks.Where(r => r.Id == c.MilkId).DefaultIfEmpty()
join storeenrolledinmilkprogram in _context.YesNos on c.StoreEnrolledInMilkProgram equals storeenrolledinmilkprogram.Id into lstoreenrolledinmilkprograms
from lstoreenrolledinmilkprogram in lstoreenrolledinmilkprograms.Where(r => r.Id == c.StoreEnrolledInMilkProgram).DefaultIfEmpty()
join insurancecheck in _context.InsuranceCheck on c.InsuranceCheckId equals insurancecheck.Id into linsurancechecks
from linsurancecheck in linsurancechecks.Where(r => r.Id == c.InsuranceCheckId).DefaultIfEmpty()
join rtcacct in _context.YesNos on c.RtcAcct equals rtcacct.Id into lrtcaccts
from lrtcacct in lrtcaccts.Where(r => r.Id == c.RtcAcct).DefaultIfEmpty()
join atmoption in _context.AtmOptions on c.AtmOptionId equals atmoption.Id into latmoptions
from latmoption in latmoptions.Where(r => r.Id == c.AtmOptionId).DefaultIfEmpty()
select new CustomerViewModel()
{
Id = c.Id,
MemberID = c.MemberCode,
FirstName = c.FirstName,
LastName = c.LastName,
OwnerName = c.OwnerName,
ContactPerson = c.ContactPerson,
CorporateName = c.CorpName,
BusinessName = c.BName,
BusinessAddress = c.BAddress,
BusinessCity = c.BCity,
BusinessState = lBState == null ? string.Empty : lBState.StateCode,
BusinessZipCode = c.BZip,
BusinessPhone = c.BPhone,
BusinessCellphone = c.BCellphone,
MailingAddress = c.MAddress,
MailingCity = c.MCity,
MailingState = LMState == null ? string.Empty : LMState.StateCode,
MailingZip = c.MZip,
Fax = c.Fax,
Email = c.Email,
SalesTaxCode = c.SalesTaxCode,
FederalTaxCode = c.FederalTaxCode,
Zone = lzone == null ? string.Empty : lzone.Zone,
District = c.District,
Region = lregion == null ? string.Empty : lregion.RegionNumber,
ZoneManager = lu == null ? string.Empty : lu.FirstName,
DateInactive = c.DateInactive == null ? c.DateInactive : c.DateInactive.Value.Date,
InactiveReason = c.InactiveReason,
DateJoined = c.DateJoined == null ? c.DateJoined : c.DateJoined.Value.Date,
SignedBy = c.SignedBy,
Comments = c.Comments,
Status = lstatus == null ? string.Empty : lstatus.Status,
StoreType = lstoretype == null ? string.Empty : lstoretype.Type,
GasBrand = lgasbrand == null ? string.Empty : lgasbrand.BrandName,
NoOfMpd = c.NoOfMpd,
PropaneBrand = lpropanebrand == null ? string.Empty : lpropanebrand.BrandName,
WasteService = lwasteservice == null ? string.Empty : lwasteservice.Name,
DonationBox = ldonationbox == null ? string.Empty : ldonationbox.Confirm,
BannerSize = c.BannerSize,
PumpTopperSize = lpumptopper == null ? string.Empty : lpumptopper.Size,
NoOfPumpTopper = c.NoOfPumpTopper,
HotFood = c.HotFood,
FountainMachine = lfountainbrand == null ? string.Empty : lfountainbrand.BrandName,
NoOfNozzels = c.NoOfNozzels,
NoOfCokeNozzels = c.NoOfCokeNozzels,
NoOfDrPepperNozzels = c.NoOfDrPepperNozzels,
NoOfPepsiNozzels = c.NoOfPepsiNozzels,
Coffee = lcoffee == null ? string.Empty : lcoffee.BrandName,
Slushy = lslushy == null ? string.Empty : lslushy.BrandName,
NoOfColdVault = c.NoOfColdVault,
NoOfCarbDoors = c.NoOfCarbDoors,
NoOfNonCarbDoors = c.NoOfNonCarbDoors,
NoOfStoreOptions = c.NoOfStoreOptions,
NoOfBeers = c.NoOfBeers,
NoOfCarbShelves = c.NoOfCarbShelves,
NoOfNonCarbShelves = c.NoOfNonCarbShelves,
NoOfCokeNonCarbs = c.NoOfCokeNonCarbs,
NoOfRedbullShelves = c.NoOfRedbullShelves,
NoOfMonsterShelves = c.NoOfMonsterShelves,
NoOfGatoradeShelves = c.NoOfGatoradeShelves,
NoOfArizonaFacings = c.NoOfArizonaFacings,
NoOfNesquickFacings = c.NoOfNesquickFacings,
NoOfAquafinaFacings = c.NoOfAquafinaFacings,
NoOfWildCatFacings = c.NoOfWildCatFacings,
NoOfJarritosFacings = c.NoOfJarritosFacings,
NoOfBangFacings = c.NoOfBangFacings,
NoOfXyiencFacings = c.NoOfXyiencFacings,
NoOfBodyArmourFacings = c.NoOfBodyArmourFacings,
ShelvesInDoor = c.ShelvesInDoor,
Milk = lmilk == null ? string.Empty : lmilk.BrandName,
NoOfMilkShelves = c.NoOfMilkShelves,
StoreEnrolledInMilkProgram = lstoreenrolledinmilkprogram == null ? string.Empty : lstoreenrolledinmilkprogram.Confirm,
InsuranceCheck = linsurancecheck == null ? string.Empty : linsurancecheck.Option,
InsuranceDate = c.InsuranceDate == null ? c.InsuranceDate : c.InsuranceDate.Value.Date,
RtcAcct = lrtcacct == null ? string.Empty : lrtcacct.Confirm,
Electricity = c.Electricity,
AtmOption = latmoption == null ? string.Empty : latmoption.Option,
AtmProcessor = c.AtmProcessor,
GasJobber = c.GasJobber,
StoreSquareFootage = c.StoreSquareFootage,
IceCream = c.IceCream,
NoOfPepsiNonCarbs = c.NoOfPepsiNonCarbs,
NoOfDrPepperNonCarbs = c.NoOfDrPepperNonCarbs,
NoOfStoreOptionShelves = c.NoOfStoreOptionShelves,
Wholesalers = c.Wholesalers,
NoOfPosters = c.NoOfPosters,
PosterSizes = c.PosterSizes,
Fritolay = c.Fritolay,
OtherSnacks = c.OtherSnacks,
StandingCooler = c.StandingCooler,
CounterTopCooler = c.CounterTopCooler,
MidSizeCooler = c.MidSizeCooler,
Created = c.Created == null ? c.Created : c.Created.Value.Date,
Createdby = c.Createdby,
Modified = c.Modified == null ? c.Modified : c.Modified.Value.Date,
Modifiedby = c.Modifiedby,
CoolerModified = c.CoolerModified == null ? c.CoolerModified : c.CoolerModified.Value.Date,
SpacePaymentEligible = c.SpacePaymentEligible
};
# endregion
return results.AsNoTracking().ToList();
Here is the query it generates in output window.
SELECT "c"."id", "c"."member_code" AS "MemberID", "c"."first_name" AS "FirstName", "c"."last_name" AS "LastName", "c"."owner_name" AS "OwnerName", "c"."contact_person" AS "ContactPerson", "c"."corp_name" AS "CorporateName", "c"."b_name" AS "BusinessName", "c"."b_address" AS "BusinessAddress", "c"."b_city" AS "BusinessCity", CASE
WHEN "t"."id" IS NULL
THEN '' ELSE "t"."state_code"
END AS "BusinessState", "c"."b_zip" AS "BusinessZipCode", "c"."b_phone" AS "BusinessPhone", "c"."b_cellphone" AS "BusinessCellphone", "c"."m_address" AS "MailingAddress", "c"."m_city" AS "MailingCity", CASE
WHEN "t0"."id" IS NULL
THEN '' ELSE "t0"."state_code"
END AS "MailingState", "c"."m_zip" AS "MailingZip", "c"."fax", "c"."email", "c"."sales_tax_code" AS "SalesTaxCode", "c"."federal_tax_code" AS "FederalTaxCode", CASE
WHEN "t4"."id" IS NULL
THEN '' ELSE "t4"."zone"
END AS "Zone", "c"."district", CASE
WHEN "t3"."id" IS NULL
THEN '' ELSE "t3"."region_number"
END AS "Region", CASE
WHEN "t2"."id" IS NULL
THEN '' ELSE "t2"."first_name"
END AS "ZoneManager", CASE
WHEN "c"."date_inactive" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."date_inactive", "c"."date_inactive", "c"."InactiveReason", CASE
WHEN "c"."date_joined" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."date_joined", "c"."date_joined", "c"."SignedBy", "c"."comments", CASE
WHEN "t1"."id" IS NULL
THEN '' ELSE "t1"."status"
END AS "Status", CASE
WHEN "t5"."id" IS NULL
THEN '' ELSE "t5"."type"
END AS "StoreType", CASE
WHEN "t6"."id" IS NULL
THEN '' ELSE "t6"."brand_name"
END AS "GasBrand", "c"."no_of_mpd" AS "NoOfMpd", CASE
WHEN "t7"."id" IS NULL
THEN '' ELSE "t7"."brand_name"
END AS "PropaneBrand", CASE
WHEN "t8"."id" IS NULL
THEN '' ELSE "t8"."name"
END AS "WasteService", CASE
WHEN "t9"."id" IS NULL
THEN '' ELSE "t9"."confirm"
END AS "DonationBox", "c"."banner_size" AS "BannerSize", CASE
WHEN "t10"."id" IS NULL
THEN '' ELSE "t10"."size"
END AS "PumpTopperSize", "c"."no_of_pump_topper" AS "NoOfPumpTopper", "c"."hot_food" AS "HotFood", CASE
WHEN "t11"."id" IS NULL
THEN '' ELSE "t11"."brand_name"
END AS "FountainMachine", "c"."no_of_nozzels" AS "NoOfNozzels", "c"."no_of_coke_nozzels" AS "NoOfCokeNozzels", "c"."no_of_dr_pepper_nozzels" AS "NoOfDrPepperNozzels", "c"."no_of_pepsi_nozzels" AS "NoOfPepsiNozzels", CASE
WHEN "t12"."id" IS NULL
THEN '' ELSE "t12"."brand_name"
END AS "Coffee", CASE
WHEN "t13"."id" IS NULL
THEN '' ELSE "t13"."brand_name"
END AS "Slushy", "c"."no_of_cold_vault" AS "NoOfColdVault", "c"."no_of_carb_doors" AS "NoOfCarbDoors", "c"."no_of_non_carb_doors" AS "NoOfNonCarbDoors", CAST("c"."no_of_store_options" AS TEXT) AS "NoOfStoreOptions", "c"."no_of_beers" AS "NoOfBeers", "c"."no_of_carb_shelves" AS "NoOfCarbShelves", "c"."no_of_non_carb_shelves" AS "NoOfNonCarbShelves", "c"."no_of_coke_non_carbs" AS "NoOfCokeNonCarbs", "c"."no_of_redbull_shelves" AS "NoOfRedbullShelves", "c"."no_of_monster_shelves" AS "NoOfMonsterShelves", "c"."no_of_gatorade_shelves" AS "NoOfGatoradeShelves", "c"."no_of_arizona_facings" AS "NoOfArizonaFacings", "c"."no_of_nesquick_facings" AS "NoOfNesquickFacings", "c"."no_of_aquafina_facings" AS "NoOfAquafinaFacings", "c"."no_of_wild_cat_facings" AS "NoOfWildCatFacings", "c"."no_of_jarritos_facings" AS "NoOfJarritosFacings", "c"."no_of_bang_facings" AS "NoOfBangFacings", "c"."no_of_xyienc_facings" AS "NoOfXyiencFacings", "c"."no_of_body_armour_facings" AS "NoOfBodyArmourFacings", "c"."shelves_in_door" AS "ShelvesInDoor", CASE
WHEN "t14"."id" IS NULL
THEN '' ELSE "t14"."brand_name"
END AS "Milk", "c"."no_of_milk_shelves" AS "NoOfMilkShelves", CASE
WHEN "t15"."id" IS NULL
THEN '' ELSE "t15"."confirm"
END AS "StoreEnrolledInMilkProgram", CASE
WHEN "t16"."id" IS NULL
THEN '' ELSE "t16"."option"
END AS "InsuranceCheck", CASE
WHEN "c"."insurance_date" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."insurance_date", "c"."insurance_date", CASE
WHEN "t17"."id" IS NULL
THEN '' ELSE "t17"."confirm"
END AS "RtcAcct", "c"."electricity", CASE
WHEN "t18"."id" IS NULL
THEN '' ELSE "t18"."option"
END AS "AtmOption", "c"."atm_processor" AS "AtmProcessor", "c"."gas_jobber" AS "GasJobber", "c"."store_square_footage" AS "StoreSquareFootage", "c"."ice_cream" AS "IceCream", "c"."no_of_pepsi_non_carbs" AS "NoOfPepsiNonCarbs", "c"."no_of_dr_pepper_non_carbs" AS "NoOfDrPepperNonCarbs", "c"."no_of_store_option_shelves" AS "NoOfStoreOptionShelves", "c"."wholesalers", "c"."no_of_posters" AS "NoOfPosters", "c"."poster_sizes" AS "PosterSizes", "c"."fritolay", "c"."other_snacks" AS "OtherSnacks", "c"."standing_cooler" AS "StandingCooler", "c"."counter_top_cooler" AS "CounterTopCooler", "c"."mid_size_cooler" AS "MidSizeCooler", CASE
WHEN "c"."created" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."created", "c"."created", "c"."createdby", CASE
WHEN "c"."modified" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."modified", "c"."modified", "c"."modifiedby", CASE
WHEN "c"."cooler_modified" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."cooler_modified", "c"."cooler_modified", "c"."SpacePaymentEligible"
FROM "customers" AS "c"
LEFT JOIN (
SELECT "BState".*
FROM "states" AS "BState"
WHERE "BState"."id" = "c"."state_id"
) AS "t" ON "c"."state_id" = "t"."id"
LEFT JOIN (
SELECT "MState".*
FROM "states" AS "MState"
WHERE "MState"."id" = "c"."m_state_id"
) AS "t0" ON "c"."m_state_id" = "t0"."id"
LEFT JOIN (
SELECT "status".*
FROM "statuses" AS "status"
WHERE "status"."id" = "c"."status_id"
) AS "t1" ON "c"."status_id" = "t1"."id"
LEFT JOIN (
SELECT "u".*
FROM "employees" AS "u"
WHERE "u"."id" = "c"."zone_manager"
) AS "t2" ON "c"."zone_manager" = "t2"."id"
LEFT JOIN (
SELECT "regions".*
FROM "regions" AS "regions"
WHERE "regions"."id" = "c"."id"
) AS "t3" ON "c"."region_id" = "t3"."id"
LEFT JOIN (
SELECT "zone".*
FROM "zones" AS "zone"
WHERE "zone"."id" = "c"."zone_id"
) AS "t4" ON "c"."zone_id" = "t4"."id"
LEFT JOIN (
SELECT "storetype".*
FROM "store_types" AS "storetype"
WHERE "storetype"."id" = "c"."store_type_id"
) AS "t5" ON "c"."store_type_id" = "t5"."id"
LEFT JOIN (
SELECT "gasbrand".*
FROM "gas_brands" AS "gasbrand"
WHERE "gasbrand"."id" = "c"."gas_brand_id"
) AS "t6" ON "c"."gas_brand_id" = "t6"."id"
LEFT JOIN (
SELECT "propanebrand".*
FROM "propane_brands" AS "propanebrand"
WHERE "propanebrand"."id" = "c"."propane_brand_id"
) AS "t7" ON "c"."propane_brand_id" = "t7"."id"
LEFT JOIN (
SELECT "wasteservice".*
FROM "waste_services" AS "wasteservice"
WHERE "wasteservice"."id" = "c"."waste_service_id"
) AS "t8" ON "c"."waste_service_id" = "t8"."id"
LEFT JOIN (
SELECT "donationbox".*
FROM "yes_nos" AS "donationbox"
WHERE "donationbox"."id" = "c"."donation_box_id"
) AS "t9" ON "c"."donation_box_id" = "t9"."id"
LEFT JOIN (
SELECT "pumptoppersize".*
FROM "pump_topper_sizes" AS "pumptoppersize"
WHERE "pumptoppersize"."id" = "c"."pump_topper_size_id"
) AS "t10" ON "c"."pump_topper_size_id" = "t10"."id"
LEFT JOIN (
SELECT "fountainbrand".*
FROM "fountain_machines" AS "fountainbrand"
WHERE "fountainbrand"."id" = "c"."fountain_machine_id"
) AS "t11" ON "c"."fountain_machine_id" = "t11"."id"
LEFT JOIN (
SELECT "coffee".*
FROM "coffees" AS "coffee"
WHERE "coffee"."id" = "c"."coffee_id"
) AS "t12" ON "c"."coffee_id" = "t12"."id"
LEFT JOIN (
SELECT "slushy".*
FROM "slushies" AS "slushy"
WHERE "slushy"."id" = "c"."slushy_id"
) AS "t13" ON "c"."slushy_id" = "t13"."id"
LEFT JOIN (
SELECT "milk".*
FROM "milks" AS "milk"
WHERE "milk"."id" = "c"."milk_id"
) AS "t14" ON "c"."milk_id" = "t14"."id"
LEFT JOIN (
SELECT "storeenrolledinmilkprogram".*
FROM "yes_nos" AS "storeenrolledinmilkprogram"
WHERE "storeenrolledinmilkprogram"."id" = "c"."store_enrolled_in_milk_program"
) AS "t15" ON "c"."store_enrolled_in_milk_program" = "t15"."id"
LEFT JOIN (
SELECT "insurancecheck".*
FROM "insurance_check" AS "insurancecheck"
WHERE "insurancecheck"."id" = "c"."insurance_check_id"
) AS "t16" ON "c"."insurance_check_id" = "t16"."id"
LEFT JOIN (
SELECT "rtcacct".*
FROM "yes_nos" AS "rtcacct"
WHERE "rtcacct"."id" = "c"."rtc_acct"
) AS "t17" ON "c"."rtc_acct" = "t17"."id"
LEFT JOIN (
SELECT "atmoption".*
FROM "atm_options" AS "atmoption"
WHERE "atmoption"."id" = "c"."atm_option_id"
) AS "t18" ON "c"."atm_option_id" = "t18"."id"
Further technical details
EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Windows 10
IDE: Visual Studio 2017 15.4
raymak23
All 7 comments
Sorry if I missed something but it seems all of those Wheres before the DefaultIfEmptys are unnecessary. What is happening here is you set it up for a left join, but then the Where causes an OUTER APPLY situation (which last I checked the EF Core query compiler, or Sqlite for that matter) does not support.) So it tries to build a left join, but then hits the Where and pushes that down into a derived table, which fails because the predicate is referencing an outer column.
tuespetre
on 13 Nov 2018
👍2
🎉1
Thanks @tuespetre. Let me give it a try and I will provide more feedback by tonight.
raymak23
on 13 Nov 2018
@tuespetre, I removed where and now it is working fine.
from c in _context.Customers
join BState in _context.States on c.StateId equals BState.Id into lBStates
from lBState in lBStates.DefaultIfEmpty()
There must be some change in EF Core v2. The original query that I posted is working fine in EF Core v1.
raymak23
on 14 Nov 2018
I have this very annoying behavior too, and I still didn't found any workaround.
Here's the code
Mailbox result = null;
var mbSet = Context.Set<Mailbox>().AsQueryable();
if (includeDossiers)
{
mbSet = mbSet.Include(m => m.Dossiers);
}
result = mbSet.AsNoTracking().FirstOrDefault(x => x.Id == id.Value);
return result;
I tried to put a ToList() between the AsNoTracking and FirstOrDefault, the exception changed from Microsoft.Data.Sqlite.SqliteException : 'SQLite Error 1: 'no such column: x.Id'.
to Microsoft.Data.Sqlite.SqliteException : 'SQLite Error 1: 'no such column: m.Id'.
I really need a workaround for this thing cause it happens every time, and removing filtering condition doesn't seems to work here …
Environment: Visual Studio 2017 (and 2019 Preview 1), EF Core 2.2, Win 10 x64, WPF App (this specific code is hosted in a netstandard dll)
cdie
on 10 Dec 2018
@cdie Can you please file a new issue with a full repro? (It's not clear to me that the root cause of your issue is the same and I was not able to reproduce with just the information above--my code is below.)
```C#
public class Mailbox
{
public int Id { get; set; }
public ICollection
}
public class Dossier
{
public int Id { get; set; }
}
public class BloggingContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlite("filename=test.db");
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Mailbox>();
}
}
public class Program
{
public static void Main()
{
int? id;
using (var context = new BloggingContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var mailbox = new Mailbox { Dossiers = new List<Dossier> { new Dossier() } };
context.Add(mailbox);
context.SaveChanges();
id = mailbox.Id;
}
using (var context = new BloggingContext())
{
Mailbox result = null;
var mbSet = context.Set<Mailbox>().AsQueryable();
mbSet = mbSet.Include(m => m.Dossiers);
result = mbSet.AsNoTracking().FirstOrDefault(x => x.Id == id.Value);
}
}
}
```
ajcvickers
on 11 Dec 2018
@ajcvickers Would love to create a new issue with repo steps, but unfortunately I cannot event reproduce it myself outside of my current WPF projet, it happens on a specific code path only (the same code works on other calls).
I can't event share my WPF with you cause it needs some specific physical access cards.
Is there a way that I can activate some specific logs or debug trace to help you guys trace this Strange issue ? I though it was my OnModelCreating that was failing, but as I said, same calls are made and it only fails on one specific code path ...
cdie
on 12 Dec 2018
We have added support for CROSS APPLY in EF Core 3.0 so first query should be working.
We don't have a full repro code here to test it out. So I am closing this issue as fixed. If you hit any exception in 3.1 release then please file a new issue with repro code.
smitpatel
on 22 Nov 2019
Was this page helpful?
0 / 5 - 0 ratings
Related issues
HappyNomad
·
3Comments
bgribaudo
·
3Comments
ghost
·
3Comments
ryanwinter
·
3Comments
julienshepherd
·
3Comments
Most helpful comment
Sorry if I missed something but it seems all of those
Wheres before theDefaultIfEmptys are unnecessary. What is happening here is you set it up for a left join, but then theWherecauses anOUTER APPLYsituation (which last I checked the EF Core query compiler, or Sqlite for that matter) does not support.) So it tries to build a left join, but then hits theWhereand pushes that down into a derived table, which fails because the predicate is referencing an outer column.