Loop over IEnumerable throw an Not Supported exception

Question

i need a query that returns only some items of a lists and i need to put these items into a database.

This is the linq query that returns an IEnumerable:

 IEnumerable<VerificaParcheggio> targheOK = from c in verifica
                                                     join d in db.Net_Veicoli_Targhe
                                                     on c.Targa equals d.Targa
                                                     join tt in db.BT_Titoli_Targhe
                                                     on new { idVeicolo = d.IDVeicolo, DataTroncata = DbFunctions.TruncateTime(c.DataUscita) } equals new { idVeicolo = tt.IDVeicolo, DataTroncata = DbFunctions.TruncateTime(tt.Scadenza) }
                                                     join td in db.BT_Titoli_Dettagli
                                                     on new { tt.IDTitolo, c.IDParcheggio } equals new { td.IDTitolo, IDParcheggio = td.IDGCPark == null ? 0 : td.IDGCPark.Value }
                                                     where tt.Attiva
                                                     && ((td.Validita == 1 && c.DataUscita.Hour <= 13) || (td.Validita == 2 && c.DataUscita.Hour > 13))
                                                     select new VerificaParcheggio() { IDParcheggio = c.IDParcheggio, DataUscita = c.DataUscita, Targa = c.Targa };

Then i want to iterate through this IEnumerable because i need to take some data from the database that i need to put into the table that i'm going to populate:

using (var transaction = db.Database.BeginTransaction())
                {

                    try
                    {

                        foreach (VerificaParcheggio v in targheOK) /*THIS LINE THROW THE EXCEPTION*/
                        {
                            BT_Verifica_Parcheggi verificaPa = (from c in db.BT_Verifica_Parcheggi
                                                               where c.idParcheggio == v.IDParcheggio &&
                                                               c.Targa == v.Targa &&
                                                               c.DataUscita == v.DataUscita
                                                               select c).FirstOrDefault();

                            decimal idVeicolo = (from c in db.Net_Veicoli_Targhe
                                             where c.Targa == v.Targa
                                             orderby c.DataOraInserimento descending
                                             select c.IDVeicolo).FirstOrDefault();

                            DatiComproprietarioVeicolo datiComproprietarioVeicolo = TitolariVeicoli.GetProprietarioPrincipaleVeicolo(idVeicolo);

                            decimal idAnagrafica = (from c in db.Net_Soggetti_Anagrafica
                                                where c.CodiceFiscale == datiComproprietarioVeicolo.codFiscale
                                                select c.IDAnagrafica).FirstOrDefault();

                            BT_Verifica_Parcheggi_KO parcheggi = new BT_Verifica_Parcheggi_KO
                            {
                                IDVerifica = verificaPa.IDVerifica,
                                idAnagrafica = idAnagrafica,
                                Esito = false
                            };
                            db.BT_Verifica_Parcheggi_KO.Add(parcheggi);
                        }

                        db.SaveChanges();
                        transaction.Commit();
catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw (ex);
                    }
                }

when i reach the foreach statement the program starts to iterate and after about 50 seconds it catch an exception saying this

"This function can only be invoked from LINQ to Entities."

Why it's generating this error?

EDIT: Reported the line when the exception is catch

Thanks a lot.

Put the Try/Catch inside the foreach instead of outside so you continue past the exception. — jdweng, Mar 10 '20 at 09:18
@MichaelRandall i add a comment on the code reporting where the catch is throw — Angelus, Mar 10 '20 at 09:21
@jdweng i need the exception here because i'm managing a transaction with the try and catch — Angelus, Mar 10 '20 at 09:24
Your foreach will trigger the exception because it's consuming the IEnumerable, but the actual throw will be somewhere inside the functions and lambdas in targheOK. Do you have a stack trace for the exception that shows what's happening beyond the foreach? — Rup, Mar 10 '20 at 09:32
I'd guess the culprit is [TruncateTime](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.dbfunctions.truncatetime) though: "Remarks: You cannot call this function directly. This function can only appear within a LINQ to Entities query." which sounds consistent with the exception you're getting: I'd guess one of the two usages aren't on values that come out of the query directly? — Rup, Mar 10 '20 at 09:34
@Rup you're probably right, i analized a bit more the exception and the problems are on the dates. Yes, one of the two truncates are on list value and not on query value. So i think i need to change that truncate. — Angelus, Mar 10 '20 at 09:45
@Rup now the exception is changed in "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." — Angelus, Mar 10 '20 at 10:06
I don't know, sorry. Can you add a 'where' so that you're only fetching a few records and see if it runs OK? Can you use your database profiling tools to check the query it is running is sensible and has a sensible execution plan? — Rup, Mar 10 '20 at 10:28
Please remove excessive indentation from your code. Help people to read your question easily (which in the end helps yourself). — Gert Arnold, Mar 10 '20 at 10:46
As for the issue: what is `verifica`? Looks like you're combining a local list with EF queryables, which automatically casts everything into `IEnumerable` (and hence disables translation of EF-specific functions into SQL). You should find another way to make this combination. — Gert Arnold, Mar 10 '20 at 10:53
Check [this](https://stackoverflow.com/questions/17366907/what-is-the-purpose-of-asqueryable), it has all your answers, IQueryable needn't be implementing the IEnumerator, that's why the error — Mrinal Kamboj, Mar 10 '20 at 10:54

score 1 · Answer 1 · answered Mar 10 '20 at 09:31

1

Looks like you have only prepared the LINQ query but which is not executed yet on the database and hence no data on your IEnumerable collection.

You may want to perform targheOK.AsEnumerable() method call before looping so that the records are dumped in-memory and you can loop through the rows.

answered Mar 10 '20 at 09:31

Raju Dasupally

164
7

Shouldn't it do that automatically when you first start enumerating the collection? I don't think an explicit AsEnumerable would make any difference here. I don't think that explains the specific exception either. – Rup Mar 10 '20 at 09:36
1

It would make a difference since the execution is the Lazy execution, a call like `AsEnumerable` or `ToList` will load all the data in the memory – Mrinal Kamboj Mar 10 '20 at 10:45

Loop over IEnumerable throw an Not Supported exception

1 Answers1