none
The entity or complex type 'myComplexType' cannot be constructed in a LINQ to Entities query Exception

    Question

  • Hi,

    I'm pulling my hair out with the following error:

    "NotSupportedException: The entity or complex type 'ChinookModel.SearchTracksForPlaylist' cannot be constructed in a LINQ to Entities query"

    I have a custom query which looks like this in SQL:

    select tr.Name, al.Title, ar.Name, ge.Name 
    from dbo.Track as tr
    join dbo.Album as al on tr.AlbumId = al.AlbumId
    join dbo.Artist as ar on al.ArtistId = ar.ArtistId
    join dbo.Genre as ge on tr.GenreId = ge.GenreId
    where tr.Name like '%tie%'
    

    I have created a complex type in my EF4 model which represents the return result (i.e. four strings).

    I am using the ADO.NET POCO Entity Generator T4 templates so I have run the custom tool to regenerate my POCO classes.

    I have then written the following method in my business layer:

    public List<SearchTracksForPlaylist> SearchTracksForPlaylist(string strSearchParameter)
        {
          if (strSearchParameter == null) throw new ArgumentNullException();
    
          var query =
            from tr in _context.Tracks
            join al in _context.Albums on tr.AlbumId equals al.AlbumId
            join ar in _context.Artists on al.ArtistId equals ar.ArtistId
            join ge in _context.Genres on tr.GenreId equals ge.GenreId
            where (tr.Name.Contains("/" + strSearchParameter + "/"))
            select (new SearchTracksForPlaylist {
              TrackName = tr.Name,
              ArtistName = al.Title,
              AlbumName = al.Title,
              Genre = ge.Name
            });
    
          return query.ToList();
          
    
    }
    

    I call this method from a console app (as you can see this is all 'proof of concept' work).

    When I complie I get no errors or warning however when I try and run the application I get the error stated earlier.

    Am I tackling this correctly? I want hte results in a strongly typed object and 'Complex Types' seemed to be the answer for custom queries i.e. where result sets don't match exactly any of the exisiting entities. I don't need to update the database from this result set so I didn't think creating an Entity object was appropriate. How can I modify the above code to avoid this issue. A google of the error has not helped me much.

    Thanks

    Stephen


    Stephen
    Wednesday, April 06, 2011 1:33 PM

Answers

  •  

    Hi Stephen,

    Welcome!

    According to your description and code, you want to query the Complex Type List, right? If I misunderstood you, please feel free to let me know. The exception means EF Provider couldn't know the Complex type. I think you could use Anonymous class and then convert them to Complex type, I write some code snippets as below:

     using (var context= new EFTestEntities2())
    
       {
    
        List<Test> testlist = new List<Test>();//test is my complex type
    
    	  //the query use Anonymous type
    
        var query = (from e in context.Employees select (new { Name = e.Name })).ToList();
    
        foreach(var q in query)
    
        {
    
         testlist.Add(new Test() { Name=q.Name });
    
        } 
    
       }
    
    
    
    

    You can move the Complex type out the query sentence, Please try it, and let me know your feedback.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Marked as answer by -Ozymandias- Thursday, April 07, 2011 5:05 PM
    Thursday, April 07, 2011 6:30 AM
  • public List<SearchTracksForPlaylist> SearchTracksForPlaylist(string strSearchParameter) {
       if (strSearchParameter == null) throw new ArgumentNullException();
    
       var query =
        from tr in _context.Tracks
        join al in _context.Albums on tr.AlbumId equals al.AlbumId
        join ar in _context.Artists on al.ArtistId equals ar.ArtistId
        join ge in _context.Genres on tr.GenreId equals ge.GenreId
        where (tr.Name.Contains("/" + strSearchParameter + "/"))
        select (new {
         TrackName = tr.Name,
         ArtistName = al.Title,
         AlbumName = al.Title,
         Genre = ge.Name
        });
    
       return query.AsEnumerable().Select(r =>new SearchTracksForPlaylist{ TrackName = r.TrackName, ArtistName => r.ArtistName, AlbumName => r.AlbumName, Genre => r.Genre} ).ToList();
    }
    
    
    Not as nice as I'd like, but this should work. 
    I believe IQueryable is unable to create instances of named classes that are not defined in .EDMX so you have to return an anonymous type, switch from IQueryable to IEnumerable and then build the class.

    Jenda 
    • Marked as answer by -Ozymandias- Thursday, April 07, 2011 5:04 PM
    Thursday, April 07, 2011 12:54 PM

All replies

  •  

    Hi Stephen,

    Welcome!

    According to your description and code, you want to query the Complex Type List, right? If I misunderstood you, please feel free to let me know. The exception means EF Provider couldn't know the Complex type. I think you could use Anonymous class and then convert them to Complex type, I write some code snippets as below:

     using (var context= new EFTestEntities2())
    
       {
    
        List<Test> testlist = new List<Test>();//test is my complex type
    
    	  //the query use Anonymous type
    
        var query = (from e in context.Employees select (new { Name = e.Name })).ToList();
    
        foreach(var q in query)
    
        {
    
         testlist.Add(new Test() { Name=q.Name });
    
        } 
    
       }
    
    
    
    

    You can move the Complex type out the query sentence, Please try it, and let me know your feedback.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Marked as answer by -Ozymandias- Thursday, April 07, 2011 5:05 PM
    Thursday, April 07, 2011 6:30 AM
  • public List<SearchTracksForPlaylist> SearchTracksForPlaylist(string strSearchParameter) {
       if (strSearchParameter == null) throw new ArgumentNullException();
    
       var query =
        from tr in _context.Tracks
        join al in _context.Albums on tr.AlbumId equals al.AlbumId
        join ar in _context.Artists on al.ArtistId equals ar.ArtistId
        join ge in _context.Genres on tr.GenreId equals ge.GenreId
        where (tr.Name.Contains("/" + strSearchParameter + "/"))
        select (new {
         TrackName = tr.Name,
         ArtistName = al.Title,
         AlbumName = al.Title,
         Genre = ge.Name
        });
    
       return query.AsEnumerable().Select(r =>new SearchTracksForPlaylist{ TrackName = r.TrackName, ArtistName => r.ArtistName, AlbumName => r.AlbumName, Genre => r.Genre} ).ToList();
    }
    
    
    Not as nice as I'd like, but this should work. 
    I believe IQueryable is unable to create instances of named classes that are not defined in .EDMX so you have to return an anonymous type, switch from IQueryable to IEnumerable and then build the class.

    Jenda 
    • Marked as answer by -Ozymandias- Thursday, April 07, 2011 5:04 PM
    Thursday, April 07, 2011 12:54 PM
  • Hi,

    Thankyou very much. Jenda, that example worked perfectly (had to replace the => with = in a few instances) thanks. I'm still a little confused why I got the error as I thought the complex type existed in my model (as I created it in the edmx).

    I have been doing more work in this area and I think I have identified three possible approaches to my problem.

    1. write a piece of Linq to Entity that returns the required results into a list of complex types

    2. use the 'include' method so I just need to grab a list of 'Track' Entities and drill into the fixups to get the required info

    3. write the SQL in a stored procedure and import them into a stored function calling this from the context.

    What are the benefits and advantages of each? from what i can tell option one requires more boiler plate code but is fast and flexible. Option 2 is easier to code and very flexible but slow to query due to all the data being returned. Option 3 I suspect will be the fastest but least flexible?

    Any opinions would be greatly appreciated and thanks for your help.

    Stephen

     

     

     


    Stephen
    Thursday, April 07, 2011 9:15 PM
  • (Re => and = ... yeah, I know. Happens to me all the time. A habit from Perl where hash literals are written as { key => value, key => value, ... }.)

     

    The third is a little more work, but it's definitely the most efficient. The database will be able to parse the query and generate the execution plan just once that way.

     

    One question though ... what does the Tracks.Name look like? It looks suspicious with the slashes and the way you search it. Is it a list of something, delimited by slashes?!?

    Keep in mind that this way you will not be able to make use of any index on the Tracks.Name. If you do store a list in the column, consider separating the values into yet another table where each item in that list will be stored separately, there will be an index on the items and your search will use trn.Name ==  strSearchParameter.

    Thursday, April 07, 2011 11:11 PM
  • Sorry for the misleading code, Tracks.Name is not a list just the name of a music track. The "/"'s in my code shouldn't be there, it was a cut and paste bug :-) code should just read:

    where (tr.Name.Contains(strSearchParameter))
    
    

    I'm using the Chinook database from CodePlex. It's a nice light weight DB designed specifically for testing O/R mapping tools.

     

     


    Stephen
    Friday, April 08, 2011 7:56 AM