SQL Linq join List VB NET

Question

Tabella_Pratiche is entities of my db, P_ListLettore is a list of object. When I try join Tabella_Pratiche and P_ListLettore I produce exception.

Dim listPraticheEsistenti As List(Of Tabella_Pratiche)

listPraticheEsistenti = (From c In DB.Tabella_Pratiche Join b In P_ListLettore On c.posizione Equals b.AssignamentID And                c.data_affido.ToString("dd/MM/yyyy") Equals b.R_RecordPA.Data_inizio_affidamento.ToString("dd/MM/yyyy") Where c.tipo_mandato = "SPG" Select c).ToList

exception " Eccezione non gestita di tipo 'System.NotSupportedException' in EntityFramework.SqlServer.dll

Ulteriori informazioni: Impossibile creare un valore costante di tipo 'ImportPratiche.RecordPrSPG'. In questo contesto sono supportati solo tipi primitivi o enumerazione. "

In the future please supply English exception messages. Alternatively there is a Spanish SO as well if you prefer to communicate in Spanish (I realize this is Italian but these are the only SO I knowof). The error is because you cannot use the join syntax between an in memory list and a list that would be accessed using Linq to Sql (still on database). If you want a close equivalent you could use Contains which is supported and will be translated into a IN clause on the database but this only supports lists of simple types like a list of int or string. — Igor
– Igor, Commented Oct 21, 2016 at 9:49
Also you cant use ToString(""), that is a .net concept and will not translate to sql. It could be that is what the problem is. I do not read Italian so I am guessing here, there might also be other issues in your code. — Igor
– Igor, Commented Oct 21, 2016 at 10:11

Alex B. · Accepted Answer · 2016-10-24 07:38:02Z

1

Like @Igor wrote in his comment, you cannot join an in-memory list of objects with a database result.
This is due to Entity Framework tries to convert the LINQ into a SQL statement and P_ListLettore is unknown in your database thus unknown to Entity Framework.

What you can do is to load data from the database first and "join" in-memory afterwards (the following code is completely untested):

    'IEnumerable with AssignamentIDs'
    Dim assignmentIDs = From p In P_ListLettore
                        Select p.AssignamentID

    'Load Tabella_Pratiche with matching IDs from DB and convert into in-memory List'
    Dim tmpPratiche = (From c In DB.Tabella_Pratiche
                       Where c.tipo_mandato = "SPG" And assignmentIDs.Contains(c.posizione)
                       Select c).ToList()

    ' Perform Join on both in-memory lists with ID and Date'
    Dim listPraticheEsistenti = (From c In tmpPratiche
                                 Join b In P_ListLettore On c.posizione Equals b.AssignamentID 
                                      And c.data_affido Equals b.R_RecordPA.Data_inizio_affidamento
                                  Select c).ToList

Be aware that you can only use a small set of .NET functions in a Linq2Sql query, Date.ToString(format) will not work for example.

edited Oct 24, 2016 at 7:38

answered Oct 21, 2016 at 12:02

Alex B.

2,1671 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ruosco82 Over a year ago

There is a problem Error Can not convert the value of type '<anonymous type> (line 130)' in '<anonymous type>

ruosco82 Over a year ago

I would not want to load P_LISTALETTORE in database

Alex B. Over a year ago

You don´t have to load P_LISTALETTORE. You have to load all Tabella_Pratiche, loop over P_LISTALETTORE and check if posizione and data_affido are equals to AssignamentID and Data_inizio_affidamento. This will be slower but should work. will edit my answer on monday when I´m back in office

Alex B. Over a year ago

btw...sorry that I posted an incorrect answer. Usually I test my code before posting...

Collectives™ on Stack Overflow

SQL Linq join List VB NET

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related