Aggregate Extension Method Not Supported (Linq to

Question

I was trying to use Aggregate Method in linq to Sql but looks like it is not supported.

LibrarySystemDataContext ctx = new LibrarySystemDataContext();

var query = ctx.Books
  .Select(x => new
      {
        BookID = x.BookID,
          BookName = x.Title,
          Authors = x.AuthorBooks.Select(a => a.Author)
          .Aggregate(new StringBuilder(), (sb, r) => sb.Append(r.FirstName + " " + r.LastName +"& "), sb => sb.ToString())
          });

to use Aggregate, i immediately converted books to IEnumerable as follows:

var query = ctx.Books.AsEnumerable()
  .Select(x => new
      {
        BookID = x.BookID,
          BookName = x.Title,
          Authors = x.AuthorBooks.Select(a => a.Author)
          .Aggregate(new StringBuilder(), (sb, r) => sb.Append(r.FirstName + " " + r.LastName +"& "), sb => sb.ToString())
          });

And, this is working. I want to know what will be the drawbacks or design flaws if i write queries like this.

Answer 1

with ctx.Books.AsEnumerable() you will select the whole books table from database and start processing it in memory. This isn't good design. What you should do is, select the data you need and then process it locally.

for example

var temp = ctx.Books
  // probably some where clause
  .Select(x => new
      {
        BookID = x.BookID,
          BookName = x.Title,
          Authors = x.AuthorBooks.Select(a => a.Author)
          })
  .ToArray();

var books = temp.Select(x => new { 
    x.BookID, 
    x.BookName, 
    Authors = x.Authors.Aggregate(new StringBuilder(), (sb, r) => sb.Append(r.FirstName + " " + r.LastName +"& "), sb => sb.ToString()) 
});