Join list into string

Question

I need to join List of string into string inside linq select. I tried that:

var myEnt = from p in ctx.Project
    select new ProjectRepository.Project
    {
        Id = p.ProjectId,
        Desc = p.ProjectDesc,
        UsersProject = String.Join("; ", (
                                from up in ctx.usersProject join u in ctx.users
                                on up.user equals u.id into uloj from uj in uloj.DefaultIfEmpty()
                                where (up.deleted ?? false) == false
                                && up.projectId == p.Id
                                && (uj.deleted ?? false) == false
                                select uj.name + " " + uj.surname).ToList())
});

gridProg.DataSource = myEnt.ToList();
gridProg.DataBind();

But i had this error:

Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery, DbRawSqlQuery) is not supported. Instead populate a DbSet with data, for example by calling Load on the DbSet, and then bind to local data. For WPF bind to DbSet.Local. For WinForms bind to DbSet.Local.ToBindingList(). For ASP.NET WebForms you can bind to the result of calling ToList() on the query or use Model Binding, for more information see http://go.microsoft.com/fwlink/?LinkId=389592.

Thank you.

UPDATE

New error after adding .Tolist() to DataSource binding.

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

Answer 1

I found the solution. I post the code below:

var usersProject = (from up in ctx.usersProject join u in ctx.users
                on up.user equals u.id into uloj from uj in uloj.DefaultIfEmpty()
                where (up.deleted ?? false) == false
                && up.projectId == p.Id
                && (uj.deleted ?? false) == false
                select new { 
                   ProjectId = up.projectId, 
                   User = uj.name + " " + uj.surname
                }).ToList();

var myEnt = from p in ctx.Project.AsEnumerable()
            select new ProjectRepository.Project
            {
               Id = p.ProjectId,
               Desc = p.ProjectDesc
            }).ToList();

 var myEntL = myEnt.ToList();
 foreach (var mysingleEnt in myEntL)
 {
    myEntL.Where(x => x.Id == mysingleEnt.Id).FirstOrDefault().utentiAssociati =
    String.Join("; ", usersProject
       .Where(x => x.ProjectId == mysingleEnt.Id).Select(x => x.User).ToList());
  }

  gridProg.DataSource = myEntL;
  gridProg.DataBind();

Thank you for help.

Answer 2

I have not tested it, but it will work. Make 2 different Queries

var Projects = (from up in ctx.usersProject join u in ctx.users
    on up.user equals u.id into uloj from uj in uloj.DefaultIfEmpty()
    where (up.deleted ?? false) == false
        && up.projectId == p.Id
        && (uj.deleted ?? false) == false
        select new { 
            ProjectId = up.projectId, 
            ProjectsList = uj.name + " " + uj.surname
        }).ToList();

var myEnt = from p in ctx.Project.AsEnumerable()
    select new ProjectRepository.Project
    {
        Id = p.ProjectId,
        Desc = p.ProjectDesc,
        UsersProject = String.Join("; ", Projects.Where(e=> p.ProjectId == e.ProjectId).Select(e=> e.ProjectsList).ToList())
    }).ToList();