24

Reading the questions here and here has given me some insight into the situation, and it seems like using the AsEnumerable is memory consuming. Is there a better way to do this LINQ and the way it is done now, is the data that comes out reliable?

Removing the AsEnumerable results in a "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator."

var results = from p in pollcards.AsEnumerable()
                          join s in spoils.AsEnumerable() on new { Ocr = p.OCR, fileName = p.PrintFilename } equals new { Ocr = s.seq, fileName = s.inputFileName }
                          where p.Version == null
                          orderby s.fileOrdering, s.seq
                          select new ReportSpoilsEntity
                          {
                              seq = s.seq,
                              fileOrdering = s.fileOrdering,
                              inputFileName = s.inputFileName,
                              Ocr = p.OCR,
                              ElectorName = p.ElectorName
                          };
Community
  • 1
  • 1
Andy
  • 2,248
  • 7
  • 34
  • 57

3 Answers3

33

AsEnumerable() is effectively a cast to IEnumerable<T>, which makes member resolution find members of Enumerable instead of Queryable. It's usually used when you want to force part of a query to run as SQL (or similar), and the remainder to run using LINQ to Objects.

See my Edulinq blog post on it for more information.

Now you've actually got two calls to AsEnumerable. I can see how removing the first but not the second could cause problems, but have you tried removing both?

var results = from p in pollcards
              join s in spoils
                 on new { Ocr = p.OCR, fileName = p.PrintFilename } 
                 equals new { Ocr = s.seq, fileName = s.inputFileName }
              where p.Version == null
              orderby s.fileOrdering, s.seq
              select new ReportSpoilsEntity
              {
                  seq = s.seq,
                  fileOrdering = s.fileOrdering,
                  inputFileName = s.inputFileName,
                  Ocr = p.OCR,
                  ElectorName = p.ElectorName
              };
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • the error i metioned came when removing both AsEnumerables. (i haven't tried removing only one). does it matter that spoils and pollcards are LINQ statements themselves? (an IQueryable and a Collection) – Andy Mar 15 '11 at 11:39
  • 1
    @Andy: Yes, the fact that `spoils` is an in-memory collection is definitely relevant. I'd assumed that was another DB query. I don't know of a way of joining half-in half-out of the database. Did `spoils` *originally* come from the database? – Jon Skeet Mar 15 '11 at 11:42
  • the spoils are created by having 2 querrys, one for the tops10 one for the last 10, doing a union on the result, and then populating the Collection by for eaching through that union. I'm not sure why we don't return the union and convert that from an Iqueryable to a collection. (would returning the IQuerryable solve the need for the AsEnumerables?) – Andy Mar 15 '11 at 11:47
  • 1
    @Andy: Leaving the union as an `IQueryable` *may* indeed fix it... give it a try :) Admittedly the SQL generated could be pretty ugly, but it may do what you want. – Jon Skeet Mar 15 '11 at 11:51
6

Using AsEnumerable will break off the query and do the "outside part" as linq-to-objects rather than Linq-to-SQL. Effectively, you're running a "select * from ..." for both your tables and then doing the joins, where clause filter, ordering, and projection client-side.

KristoferA
  • 12,287
  • 1
  • 40
  • 62
4

Be careful when using AsEnumerable with Entity Framework; if your table has lots of data your query may be slow because the query will first load data and then apply where clause, ordering and projection.

d219
  • 2,707
  • 5
  • 31
  • 36
Damith Asanka
  • 934
  • 10
  • 12