none
How to fix "Local sequence cannot be used in LINQ to SQL..."????? RRS feed

  • Question

  • Hi,

    I keep having problems with the NotSupportedException: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

    The problem: I have fetched a single object from my database. I need to run a query on a set of objects it is related to. But I also need to run a couple count queries on a set of objects from a table that has no FK relationships (as it is to contain historical data; i.e. if there are changes to other values, this data should remain intact).

    Originally, the table I used had FKs, but then I ran into issues because other data would change (i.e. an item gets deleted), and it broke the original table, as historical data got deleted (or I couldn't delete the item).

    So I made a new, similar table without the FKs, and now I can't figure out how to make it work.

    First, the main single object:

    var lab = ctx.Labs.Single(l => l.Name == id);
    Then the original query, when FKs were present:

    var counts = lab.LabComputers
      .OrderByDescending(c => c.Computer.Name)
      .Select(c => new
      {
        Name = c.Computer.Name,
        // Ensure we only count packages that are supposed to be in the lab
        Packages = c.Computer.PackageHistories.Where(h => lab.Set.SetItems.Any(i => i.Package == h.Package)).Count()
      });
    Then the new query, without any FKs to the historical data table:

    var counts = lab.LabComputers
      .OrderByDescending(c => c.Computer.Name)
      .Select(c => new
      {
        Name = c.Computer.Name,
        Packages = ctx.InstallationHistories.Where(h => h.ComputerName == c.Computer.Name && lab.Set.SetItems.Any(i => i.Package.Code == h.PackageSource)).Count()
      });
    I'm pretty sure I need to use some sort of join syntax to get the equivalent results, but I can't think how to do this. I tried using .Join(), but that caused the same exception to be raised. I'm running out of ideas...

    Perhaps I should still have one FK? One to the Computers table since historical data for computers not in the database is probably non-sensical. And maybe that'd give me a way around the local sequence issue... I don't know.

    Thanks,

    Jessica
    Thursday, August 27, 2009 10:02 PM

Answers

  • Except that as I stated, the table has no foreign keys. Anyways, I found a solution.

    var counts = from lc in lab.LabComputers
    	    // lc.Computer.Name gives us the name
    	    let allPackages = lc.Lab.Set.SetItems.Select(i => i.Package.Code)
    	    // allPackages gives us... all packages defined by the lab
    	    let historys = from h in ctx.InstallationHistories
    			 where h.ComputerName == lc.Computer.Name
    			 select h.PackageSource
    	    let packages = allPackages.Intersect(historys).Count()
    	    let extras = historys.Count() - packages
    	    select new ComputerRolloutData
    	    {
    	         Name = lc.Computer.Name,
    	         Packages = packages,
    	         Extras = extras
    	    };
    I haven't looked at the SQL it creates, but it works.
    • Marked as answer by jessic4h Wednesday, September 9, 2009 3:34 AM
    Wednesday, September 9, 2009 3:34 AM

All replies

  • Hi jessic4h,

     

    Will left outer join in LINQ to SQL works for you?

    You can refer to this blog:

    http://bhaidar.net/cs/archive/2007/08/01/left-outer-join-in-linq-to-sql.aspx

     

    If you have any questions or concerns, please update the thread and we will have a further discussion.

     

    Best Regards

    Yichun Feng

    Monday, August 31, 2009 6:30 AM
  • Hi Yichun,

    I've had a read of the link, but it can't quite figure out how to make it work for me.

    The problem is I kind of need to do a set intersection of two sequences on a custom compare.

    I fetch all the computers in LabComputers, then I find the "set" for the lab, and from that, I need to get all "packages" in the "set" which are also in the installation history for the target computer.

    So, I'm not really sure how to use the join syntax to achieve this...

    Currently, I've worked around it by using a big foreach loop, and adding individual rows/results into a List. Which is fine, except it takes a really, really long time to run. Presumably because of all the individual LINQ to SQL 'sub-queries' that get sent out.

    Thanks,

    Jessica
    Monday, August 31, 2009 10:57 PM
  • Hi Jessica,

    Please try the following query:

    ================================================================
    var counts = lab.LabComputers.OrderyByDescending(c => c.Computer.Name)
           .Select(c => new
           {
                Name = c.Computer.Name,
                Packages = c.Computer.PackageHistories.Where(h => lab.Set.SetItems.Select(
                s => s.Package).Contains(h.Package)).Count()
           };
    ================================================================

    If you want to perform a JOIN query, please provide us with more detailed information about the data table structure. 


    Have a nice day!

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, September 2, 2009 1:35 PM
    Moderator
  • Except that as I stated, the table has no foreign keys. Anyways, I found a solution.

    var counts = from lc in lab.LabComputers
    	    // lc.Computer.Name gives us the name
    	    let allPackages = lc.Lab.Set.SetItems.Select(i => i.Package.Code)
    	    // allPackages gives us... all packages defined by the lab
    	    let historys = from h in ctx.InstallationHistories
    			 where h.ComputerName == lc.Computer.Name
    			 select h.PackageSource
    	    let packages = allPackages.Intersect(historys).Count()
    	    let extras = historys.Count() - packages
    	    select new ComputerRolloutData
    	    {
    	         Name = lc.Computer.Name,
    	         Packages = packages,
    	         Extras = extras
    	    };
    I haven't looked at the SQL it creates, but it works.
    • Marked as answer by jessic4h Wednesday, September 9, 2009 3:34 AM
    Wednesday, September 9, 2009 3:34 AM