LINQ to Entity Framework many to many with list of strings

Question

This question is continuation from: Can't think of query that solves this many to many

This LINQ query is recommended to me by user @juharr, i just added string concatenation in purpose of grouping first and last name into full name.

var courseViews = from c in db.Courses  
                  select new CourseView()  
                  {  
                      CourseID = c.ID,  
                      ProfessorName = (from l in c.Leturers   
                                       where l.Is_Professor   
                                       select l.LastName+" "+l.FirstName).FirstOrDefault(),  
                      AssistantNames = (from l in c.Leturers   
                                        where !l.Is_Professor   
                                        select l.LastName+" "+l.FirstName)
                                        .ToList()   //hmmm problem
                  };

ModelView that i used is another possible cause of problems:

public class CourseView
    {
        public int ID { get; set; }
        public string CourseName { get; set; }
        public string ProfessorName { get; set; }
        public List AssistantNames { get; set; }
    }

Hmm List of strings for Assistant names problematic isn't it? At the end of my stupidity, in View i looped through this list with @foreach(var s in item.AssistantNames){@s}
@Ladislav suggested using IQueryable instead of string,how where?

For solution that i made so far i get following error:

LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[System.String] ToList[String](System.Collections.Generic.IEnumerable1[System.String])' method, and this method cannot be translated into a store expression.

Need help!

public List AssistantNames { get; set; } could be public List<string> AssistantNames { get; set; } — Bala R
– Bala R, Commented May 30, 2011 at 1:20

Andrew Cooper · Accepted Answer · 2011-05-30 01:27:04Z

1

Drop the ToList() call and then change the Assistants property to this:

public IQueryable AssistantNames { get; set; }

answered May 30, 2011 at 1:27

Andrew Cooper

32.7k5 gold badges86 silver badges124 bronze badges

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

Comments

Bala R · Accepted Answer · 2011-05-30 01:34:28Z

0

See if this works

var courseViews = from c in db.Courses
                  let assistantsList =  (from l in c.Leturers   
                                        where !l.Is_Professor   
                                        select l.LastName+" "+l.FirstName).ToList()   
                  select new CourseView()  
                  {  
                      CourseID = c.ID,  
                      ProfessorName = (from l in c.Leturers   
                                       where l.Is_Professor   
                                       select l.LastName+" "+l.FirstName).FirstOrDefault(),  
                      AssistantNames =  assistantsList
                  };

answered May 30, 2011 at 1:34

Bala R

109k23 gold badges203 silver badges212 bronze badges

Comments

BrokenGlass · Accepted Answer · 2011-05-30 01:38:15Z

Another approach would be, since you are materializing all courses anyway to split the query in two parts, the first one materializes the data, the second one uses it to create the course views (this is now a Linq to Objects query):

var courses = (from c in db.Courses 
               select new { c.ID, Leturers = c.Leturers.ToList() }).ToList();

var courseViews = from c in courses 
                  select new CourseView()  
                  {  
                      CourseID = c.ID,  
                      ProfessorName = (from l in c.Leturers   
                                       where l.Is_Professor   
                                       select l.LastName+" "+l.FirstName).FirstOrDefault(),  
                      AssistantNames = (from l in c.Leturers   
                                        where !l.Is_Professor   
                                        select l.LastName+" "+l.FirstName)
                                        .ToList()  
                  };

Collectives™ on Stack Overflow

LINQ to Entity Framework many to many with list of strings

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related