SQL select query excluding a value in EF CORE

Question

I have the LINQ query with the simple select statement which maps the columns from a table to the custom entity. I have quite a few fields so I'm trying to select one by one and leave one field out. This way seems pretty silly to me. There was an option such as not select in SQL, it should have a counterpart in the entity framework. I don't want to select the image area in this query. Do I have to write such a long code for this?

  var result = from ch in _tenantDbContext.CariHesap
                         where ch.KartTipi == cariHesapKartTip
                         select new CariHesap
                         {
                             Id = ch.Id,
                             HesapKodu = ch.HesapKodu,
                             Unvan = ch.Unvan,
                             Ad = ch.Ad,
                             Soyad = ch.Soyad,
                             AktifPasif = ch.AktifPasif,
                             KartTipi = ch.KartTipi,
                             IslemTipi = ch.IslemTipi,
                             BakGostSekli = ch.BakGostSekli,
                             DvzTL = ch.DvzTL,
                             DovizCinsi = ch.DovizCinsi,
                             KrediLimiti = ch.KrediLimiti,
                             DvzKrediLimiti = ch.DvzKrediLimiti,
                             BolgeKod = ch.BolgeKod,
                             OzelKod = ch.OzelKod,
                             GrupKod = ch.GrupKod,
                             TipKod = ch.TipKod,
                             MhsKod = ch.MhsKod,
                             MasrafMerkezi = ch.MasrafMerkezi,
                             VergiDairesi = ch.VergiDairesi,
                             HesapNo = ch.HesapNo,
                             FaturaChk = ch.FaturaChk,
                             IskontoOran = ch.IskontoOran,
                             OpsiyonGunu = ch.OpsiyonGunu,
                             OdemeGunu = ch.OdemeGunu,
                             OdemePlani = ch.OdemePlani,
                             KulSatisFiyat = ch.KulSatisFiyat,
                             CHKodu = ch.CHKodu,
                             KDVTevfikatUygula = ch.KDVTevfikatUygula,
                             KDVMuaf = ch.KDVMuaf,
                             KDVMuafAck = ch.KDVMuafAck,
                             FormBaBsUnvan = ch.FormBaBsUnvan,
                             SirketEMail = ch.SirketEMail,
                             SirketWebAdres = ch.SirketWebAdres,
                             SatIslemEMail = ch.SatIslemEMail,
                             SatAlmaIslemEMail = ch.SatAlmaIslemEMail,
                             FinIslemEMail = ch.FinIslemEMail,
                             UnvanFaturadan = ch.UnvanFaturadan,
                             EFatSenaryo = ch.EFatSenaryo,
                             EIrsSenaryo = ch.EIrsSenaryo,
                             EMMSenaryo = ch.EMMSenaryo,
                             EFatEtiket = ch.EFatEtiket,
                             EIrsEtiket = ch.EIrsEtiket,
                             MusBrmSubeTanim = ch.MusBrmSubeTanim,
                             MusBrmSubeDeger = ch.MusBrmSubeDeger,
                             MusCHKoduTanim = ch.MusCHKoduTanim,
                             MusCHKoduDeger = ch.MusCHKoduDeger,
                             LegalEntityID = ch.LegalEntityID,
                             LegalEntityName = ch.LegalEntityName,
                             EArsivTeslimSekli = ch.EArsivTeslimSekli,
                             EArsivTeslimEMail1 = ch.EArsivTeslimEMail1,
                             EArsivTeslimEMail2 = ch.EArsivTeslimEMail2,
                             EArsivTeslimEMail3 = ch.EArsivTeslimEMail3,
                             FaturaXSLT = ch.FaturaXSLT,
                             IrsaliyeXSLT = ch.IrsaliyeXSLT,
                             SGKChk = ch.SGKChk,
                             SGKFaturaTipi = ch.SGKFaturaTipi,
                             KamuChk = ch.KamuChk,
                             Kod1 = ch.Kod1,
                             Kod2 = ch.Kod2,
                             Kod3 = ch.Kod3,
                             Kod4 = ch.Kod4,
                             Kod5 = ch.Kod5,
                             Kod6 = ch.Kod6,
                             Kod7 = ch.Kod7,
                             Kod8 = ch.Kod8,
                             Kod9 = ch.Kod9,
                             Kod10 = ch.Kod10,
                             Kod11 = ch.Kod11,
                             Kod12 = ch.Kod12,
                             Kod13 = ch.Kod13,
                             Kod14 = ch.Kod14,
                             Kod15 = ch.Kod15,
                             Nesne1 = ch.Nesne1,
                             Nesne2 = ch.Nesne2,
                         };

[Entity framework - exclude list of values](https://stackoverflow.com/questions/1740987/entity-framework-exclude-list-of-values) — Damini Suthar, Sep 16 '22 at 06:19
I think Hakan is trying to exclude certain properties from the class. Not filtering. — Yat Fei Leong, Sep 16 '22 at 06:22
Yes.there is only one field in the query that I don't want.Here I have to write all but that. @YatFeiLeong — Hakan Kaban, Sep 16 '22 at 06:31
*"There was an option such as not select in SQL..."* I don't think there is, do you have an example? I believe LINQ (thus EF Core) has the same `select` capabilities as SQL, except "*" (select all), but neither have syntax for "excluding" fields. — Ivan Stoev, Sep 16 '22 at 07:04
@HakanKaban Why don't you want to use a DTO? Too many lines of code? The general consensus is a DTO for this exact purpose https://stackoverflow.com/a/45859186/495455 Remember you can always put; `multiple; lines; of; code; on; one; line;` — Jeremy Thompson, Sep 16 '22 at 07:10
@JeremyThompson for looking for an easier way. Using DTO is one way of course. — Hakan Kaban, Sep 16 '22 at 07:20
One possible relatively simple option is to eliminate LINQ projection (select) at all, by using separate fake entities and table splitting (does not change the database structure, only the in memory model of an entity - see https://stackoverflow.com/questions/70685674/how-to-delay-lazy-load-a-binary-property-byte-in-entity-framework-core-6-c/70686689#70686689 — Ivan Stoev, Sep 16 '22 at 07:21
@IvanStoev reasonable solution. But I'm actually looking for a way to handle this in the query. — Hakan Kaban, Sep 16 '22 at 07:25
Then AutoMapper [Projection](https://docs.automapper.org/en/stable/Queryable-Extensions.html) with configured [Explicit expansion](https://docs.automapper.org/en/stable/Queryable-Extensions.html#explicit-expansion) would do. Note that AM does not mean mapping to/from DTO - you can create mapping for the same type, e.g. `config.CreateMap().ForMember(dst => dst.PropToExcludeFromSelect, opt => opt.ExplicitExpansion());`. I guess it's also possible to create custom extension method, but it won't work for subqueries inside root query expression tree. — Ivan Stoev, Sep 16 '22 at 07:47
While it is possible to write such extension method, what to do with navigation properties? Or they should ne included by default? — Svyatoslav Danyliv, Sep 16 '22 at 10:28
Upvote here for 7.0 https://github.com/dotnet/efcore/issues/1387 — joakimriedel, Sep 18 '22 at 15:14

SQL select query excluding a value in EF CORE

0 Answers0