none
Linq code compiles but crashes RRS feed

  • Question

  • I'm new to Linq.  I have a data layer with a LinqToSql data source.  All I want to do is add an 'empty' record to a list for use in a dropdown.

    The code is as follows.  It compiles but crashes when called, with the error: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator. 

     public static IQueryable<MyRecord> getData(MyDataContext DbContext)
     {          
                          
         IQueryable<MyRecord> p = from u in DbContext.MyRecords
                                  where u.Inactive == false
                                  select u;
         var results = p.Concat(new MyRecord[] { new MyRecord { Id = -1, Desc = "Empty", Inactive=false } });
                       
         return results;

       }

    Friday, December 7, 2012 9:54 PM

Answers

  • Actually, it is not necessary to change the return type to IEnumerable<MyRecord>:

    public static IQueryable<MyRecord> getData(MyDataContext DbContext)
    {           
       IQueryable<MyRecord> p = from u in DbContext.MyRecords
                                where u.Inactive == false
                                select u;
       var plistP = p.ToList();
       var results = plistP.Concat(new MyRecord[] {
          new MyRecord { Id = -1, Desc = "Empty", Inactive=false } });
                         
       return results.AsQueryable<MyRecord>();
    }
    


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    We are here to learn, to share knowledge, and to earn points; all in about equal measure.

    • Marked as answer by B. Chernick Monday, December 10, 2012 1:49 PM
    Saturday, December 8, 2012 3:37 PM

All replies

  • Try this:

    public static IEnumerable<MyRecord> getData(MyDataContext DbContext) { IQueryable<MyRecord> p = from u in DbContext.MyRecords where u.Inactive == false select u; var plistP = p.ToList(); var results = plistP.Concat(new MyRecord[] {
    new MyRecord { Id = -1, Desc = "Empty", Inactive=false } }); return results; }


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    We are here to learn, to share knowledge, and to earn points; all in about equal measure.


    Friday, December 7, 2012 10:20 PM
  • Hi B. Chernick;

    The exception you are getting, "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator. ", The reason for the error is as Pieter posted in his solution. The exception happens because your first query stored in the variable p does not get executed until your second query uses it. This is because Linq query work in deferred executed mode, meaning it gets executed when something causes the query to be enumerated over which happens in the second query. When Pieter added the ToList() method to it, it causes the query to be enumerated to convert it to a List.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, December 8, 2012 2:58 PM
  • Actually, it is not necessary to change the return type to IEnumerable<MyRecord>:

    public static IQueryable<MyRecord> getData(MyDataContext DbContext)
    {           
       IQueryable<MyRecord> p = from u in DbContext.MyRecords
                                where u.Inactive == false
                                select u;
       var plistP = p.ToList();
       var results = plistP.Concat(new MyRecord[] {
          new MyRecord { Id = -1, Desc = "Empty", Inactive=false } });
                         
       return results.AsQueryable<MyRecord>();
    }
    


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    We are here to learn, to share knowledge, and to earn points; all in about equal measure.

    • Marked as answer by B. Chernick Monday, December 10, 2012 1:49 PM
    Saturday, December 8, 2012 3:37 PM
  • I'm having the task like you described above, very frequently and I'd suggest you not to do it with LINQ.
    I'm always inserting an empty item in the dropdown  after binding data to it rather than put the empty row in datasource.


    Please Mark as Reply and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.net/

    Sunday, December 9, 2012 7:19 PM