Linq throws exception on .Substring()

问题:

I've got a situation where I need to have my LINQ to Entities query return a substring depending on the length of the string. Here's the Query:

var query = (
    from f in Context.Files
    orderby f.DateAdded descending
    select new
    {
        Concerns = f.Concerns.Name,
        Sender = f.Sender.Name,
        CategoryCount = f.Categories.Count(),
        DateAdded = f.DateAdded,
        Comment = (f.Comment == null || f.Comment.Length < 5) 
            ? f.Comment : f.Comment
    }).Take(10);

So what I'm doing is getting the last 10 added Entities of type Files and then select a set of properties from it to display inside a listview. Some are plain strings (Concerns, Sender). CategoryCount returns the number of categories which are associated with the File object.

However, I want the comment to be truncated if it is longer then a given length. In the above code, everything is working correctly. Now when I replace this line:

Comment = (f.Comment == null || f.Comment.Length < 5) 
    ? f.Comment : f.Comment

With this line:

Comment = (f.Comment == null || f.Comment.Length < 5) 
    ? f.Comment : f.Comment.SubString(0,5)

the application throws a XamlParseException (???)

The invocation of the constructor on type 'DocumentManager.Views.ListEntriesView' that matches the specified binding constraints threw an exception

I really don't know why it would do that. Is the SubString method not supported in LINQ?

Hope someone can help me here. Until then, I'll just leave it the way it is.

EDIT 2 (Somehow, my first edit got lost. So I'm redoing it): Based on the comments I got I changed my code to this and it works now:

var query = App.Context.Files.OrderByDescending(File => File.DateAdded).Take(10).AsEnumerable()
            .Select(File => new
            {
                Concerns = File.Concerns.Name,
                Sender = File.Sender.Name,
                CategoryCount = File.Categories.Count(),
                DateAdded = File.DateAdded,
                Comment = (File.Comment == null || File.Comment.Length < 100) ? File.Comment : File.Comment.Substring(0, 100) + "..."
            });

I forgot to mention that I'm using SQLite. So maybe Substring is not implemented in the SQLite EF Provider.

回答1:

I don't know for sure, but I would suspect substring isn't supported by Linq-to-Entities. I would move your Take(10) to before your select statement, then after Take(10) call AsEnumerable(), then have your select statement after that. That will cause you to pull back a collection of Files from the database, then the projection will be done in-memory.

回答2:

It's not LINQ's fault actually. Your model is bind to IQueryable, i.e. the routines directly supported by your database (everything else throws Exceptions). You should use AsEnumerable method at some point to do everything else.

Read more as Bill Wagner explains difference between IQueryable and IEnumerable here:

http://msdn.microsoft.com/en-us/vcsharp/ff963710

回答3:

This appears to be a bug in the SQLite parser because

• Substring works correctly to query into a SQL Server database with LINQ to Entities

• If you look in the generated SQL log, it generates it as Substring SQL function

• In SQLite, the correct function is substr, not substring

Thus, there is a bug in the way it is generating the query.

Here's a way to fix this bug.

In your database model, add this code right before EntityContainer

<Function Name="substr" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" ReturnType="nvarchar">
  <Parameter Name="text" Type="nvarchar" Mode="In" />
  <Parameter Name="startPos" Type="int" Mode="In" />
</Function>

In your context class (create a partial class next to it), add this code

[DbFunction("MyModel.Store", "substr")]
public string SubStr(string text, int startPos) {
    return text.Substring(startPos);
}

In your code, call Substring in this way

context.SubStr(text, startpos)

It will now properly map to the SUBSTR function instead of SUBSTRING! It's like mapping a User Defined Function, except that we map to an existing standard function.

Hope this helps!

回答4:

Correct - LINQ doesn't support substring, but it's not always very clear from the exception when you try things like that unfortunaltely.