Unsupported Linq Operations

Normal 0 false false false EN-US X-NONE HE MicrosoftInternetExplorer4 Hi, I'm trying to figure out what exactly can/can't one do with Linq/Lightspeed.

I have the following piece of code that is throwing a NotSuportedException...

I would like to know if there is a way for these sort of queries to work out of the box, or do I need to be aware at every point to what Lightspeed supports and what not?

I would expect Lightspeed to realize it can't make this magically work through the data base, and to run the query locally... Am I being too optimistic about the way things should be?

var targeFiles = files.ToDictionary(x => x.Filename);

// Find out which files aren't on the disk anymore but are in the db
var deletedFiles = from f in unitOfWork.Files
                          where (!targeAx1s.ContainsKey(f.Filename))
                          select f;

The Files Entity is defined as:

  [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [Table("files")]
  public partial class File : Entity<int>
  {
    #region Fields
    [Column("filename")]
    [ValidatePresence]
    [ValidateLength(0, 736)]
    private string _filename;
    [Column("import_date")]
    private System.DateTime _importDate;
    #endregion
     
    #region Properties
    public string Filename
    {
      get { return Get(ref _filename); }
      set { Set(ref _filename, value, "Filename"); }
    }

    public System.DateTime ImportDate
    {
      get { return Get(ref _importDate); }
      set { Set(ref _importDate, value, "ImportDate"); }
    }
    #endregion
  }

 

Executing the query generates the following exception:

System.NotSupportedException was unhandled
  Message="The method 'ContainsKey' is not supported."
  Source="Mindscape.LightSpeed.Linq"
  StackTrace:
       at Mindscape.LightSpeed.Linq.TranslatableExpressions.TranslateMethod(MethodInfo method)
       at Mindscape.LightSpeed.Linq.LinqQueryTranslator.VisitMethodCall(MethodCallExpression m)
       at Mindscape.LightSpeed.Linq.ExpressionVisitor.Visit(Expression exp)
       at Mindscape.LightSpeed.Linq.LinqQueryTranslator.VisitUnary(UnaryExpression u)
       at Mindscape.LightSpeed.Linq.ExpressionVisitor.Visit(Expression exp)
       at Mindscape.LightSpeed.Linq.Sqo.Where.OnVisit(LinqQueryTranslator visitor, MethodCallExpression expression)
       at Mindscape.LightSpeed.Linq.Sqo.SqoBase.PerformVisit(LinqQueryTranslator visitor, MethodCallExpression expression)
       at Mindscape.LightSpeed.Linq.LinqQueryTranslator.VisitMethodCall(MethodCallExpression m)
       at Mindscape.LightSpeed.Linq.ExpressionVisitor.Visit(Expression exp)
       at Mindscape.LightSpeed.Linq.LinqQueryTranslator.Translate(Expression expression, IUnitOfWork unitOfWork)
       at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
       at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
       at Mindscape.LightSpeed.Linq.LinqQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at Scanner.DeleteFiles(IEnumerable`1 files)
       at Scanner.Run()
       at Scanner.Program.Main(String[] args)
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:
 

 

Although a bit annoying, this is consistent with other LINQ database providers such as LINQ to SQL: if the operation cannot be processed on the database, the provider will fail the operation rather than trying to run it locally.  If you try your example under LINQ to SQL then you will see the same result (actually, LINQ to SQL gives a slightly nicer error message than we do, but the upshot is the same).

The reason for this is that if the provider were to fall back on performing the operation on the client, it could result in a very large SELECT clogging the database and the network, followed by massive processing on the client.  Therefore, LINQ makes the programmer expressly indicate that they believe it is safe and want the query to run locally using an operator such as ToList() or AsEnumerable().

Therefore, when using CLR methods in LINQ to SQL or LINQ to LightSpeed, I'm afraid you do need to know which CLR methods are supported and which are not, because the "run on the database or fail" philosophy says that a LINQ provider should support only those CLR methods which can be translated into SQL.  Worse still, this may vary from database to database: for example MySQL and PostgreSQL provide a wider range of SQL functions than SQLite, so some CLR methods that are translatable when the database is MySQL become untranslatable (and will cause the query to fail) when the database is SQLite.

As a general guideline, LINQ to LightSpeed supports the following CLR methods:

* String members with SQL equivalents such as Length and Replace, or that can be translated into LIKE clauses, such as StartsWith, EndsWith and Contains

* Arithmetic operations (+, -, *, /, modulus)

* Math members (e.g. trigonometric functions) where supported by the database 

* DateTime properties e.g. Year, Month, Day, with some per-database limitations especially around time properties

Members of classes other than String, Math and DateTime are generally not supported.

What does this mean for your query, where you want to use a method (Dictionary.ContainsKey) that doesn't have a translation into SQL, but where you know it is acceptable to run it on the client instead?  It means you need to cut LightSpeed out of the query, or at least out of the part of it that uses the untranslatable method.  Thus, you use LightSpeed to explicitly load all the Files, and then run the query against the File objects as a LINQ to Objects query.  You can do this as follows:

var targeFiles = files.ToDictionary(x => x.Filename);
var dbFiles = unitOfWork.Files.ToList();
var deletedFiles = from f in dbFiles
                   where (!targeFiles.ContainsKey(f.Filename))
                   select f;

The ToList() operator forces LINQ to LightSpeed to load *all* the Files in the database; then because dbFiles is no longer a LightSpeed query but merely a .NET List<T>, the filter runs locally using LINQ to Objects.

Another possible syntax for this is:

var targeFiles = files.ToDictionary(x => x.Filename);
var deletedFiles = from f in unitOfWork.Files.AsEnumerable()
                   where (!targeFiles.ContainsKey(f.Filename))
                   select f;

The AsEnumerable() operator forces the C# compiler to use LINQ to Objects instead of LINQ to LightSpeed (because it makes the compiler treat unitOfWork.Files as an IEnumerable<File> rather than an IQueryable<File>).

Hope this makes sense; let me know if you need more information, or if the explanation or fixes are not clear. 

Thanks for the detailed replay.

I solved the problem the very same way you suggested, but somewhere along the way I was

hoping that there a:

unitOfWork.LeaveMeAloneIKnowWhatImDoing = true;