Alternative to using String.Join in Linq query

I am trying to use the Entity Framework in my ASP MVC 3 site to bind a Linq query to a GridView datasource. However since I need to pull information from a secondary table for two of the fields I am getting the error

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.

I would like to be able to do this without creating a dedicated view model. Is there an alternative to using String.Join inside a Linq query?

var grid = new System.Web.UI.WebControls.GridView();

//join a in db.BankListAgentId on b.ID equals a.BankID
var banks = from b in db.BankListMaster
    where b.Status.Equals("A")
    select new
    {
        BankName = b.BankName,
        EPURL = b.EPURL.Trim(),
        AssociatedTPMBD = b.AssociatedTPMBD,
        FixedStats = String.Join("|", from a in db.BankListAgentId
                                      where a.BankID == b.ID &&
                                      a.FixedOrVariable.Equals("F")
                                      select a.AgentId.ToString()),
        VariableStats = String.Join("|", from a in db.BankListAgentId
                                         where a.BankID == b.ID &&
                                         a.FixedOrVariable.Equals("V")
                                         select a.AgentId.ToString()),
        SpecialNotes = b.SpecialNotes,
    };

grid.DataSource = banks.ToList();
grid.DataBind();

Answers


If you're not overly worried about performance (since it has subqueries, it may generate n+1 queries to the database, and if the database rows are large, you may fetch un-necessary data), the simplest fix is to add an AsEnumerable() to do the String.Join on the web/application side;

var banks = (from b in db.BankListMaster 
             where b.Status.Equals("A") select b)
            .AsEnumerable() 
            .Select(x => new {...})

At the point of the call to AsEnumerable(), the rest of the Linq query will be done on the application side instead of the database side, so you're free to use any operators you need to get the job done. Of course, before that you'll want to filter the result as much as possible.


Need Your Help

Joomla 3 MVC platform 12.1 custom component template override

model-view-controller joomla components joomla-extensions joomla3.0

I'm working with Joomla 3's MVC 12.1 platform and running into some issues with getting template overrides to work.

Rotating a 2d array -> reversing a loop

c arrays algorithm loops trigonometry

I use the following code to rotate a bitmask (a packed 2d array). To be honest I do not have a firm grip of the algorithm used, I copied and modified the rotation code from the pygame library (wher...