Linq query does not work when compiled

Question

I have more than 100 compiled query but this one causes problem. This is my exact compiled Query:

public static Func<DatabaseDataContext, int, string, int, byte, NameCommentPageResult>
           GetNameComments = CompiledQuery.Compile((DatabaseDataContext db, int NameId, string UserId, int Start, byte Count)
        => new NameCommentPageResult
        {
            Count = db.NameComments.Count(q => q.NameId == NameId && q.VerifiedBy != "-1"),
            Name = db.Names.First(n => n.ID == NameId).Name1,
            Comments = db.NameComments.Where(c => c.NameId == NameId && c.VerifiedBy != "-1").Select(c => new NameCommentResult
            {
                Datetime = c.Datetime,
                Id = c.Id,
                NameId = c.NameId,
                UserId = c.UserId,
                UserVoted = db.NameCommentVotes.Any(v => v.UserId == UserId && v.CommentId == c.Id),
                UserDisplayName = db.AspNetUserClaims.Any(cl => cl.ClaimType == "DisplayName" && cl.ClaimValue != "" && cl.User_Id == c.UserId) ? db.AspNetUserClaims.Where(cl => cl.ClaimType == "DisplayName" && cl.ClaimValue != "" && cl.User_Id == c.UserId).First().ClaimValue : db.AspNetUsers.Where(u => u.Id == c.UserId).First().UserName,
                UserPhoto = db.AspNetUserClaims.Where(cl => cl.User_Id == c.UserId && cl.ClaimType == "Image").SingleOrDefault().ClaimValue,
            }).OrderByDescending(o => o.Datetime).Skip(Start - 1).Take(Count).ToArray()
        });

I'm getting the error:

Value cannot be null. Parameter name: value

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.ArgumentNullException: Value cannot be null. Parameter name: value

But when I run same code without compiling it, it works fine, what is the problem?

this works fine:

int Start = 1; byte Count = 10; int NameId = 100; 
    using (DatabaseDataContext db = new DatabaseDataContext())
                {
                    //Result = _Names.Comments.GetNameComments(db, Id, User == null ? "" : User.Id, 1, (byte)10);
                    Result = new NameCommentPageResult
                       {
                           Count = db.NameComments.Count(q => q.NameId == Id && q.VerifiedBy != "-1"),
                           Name = db.Names.First(n => n.ID == Id).Name1,
                           Comments = db.NameComments.Where(c => c.NameId == Id && c.VerifiedBy != "-1").Select(c => new NameCommentResult
                           {
                               Datetime = c.Datetime,
                               Id = c.Id,
                               NameId = c.NameId,
                               UserId = c.UserId,
                               UserVoted = db.NameCommentVotes.Any(v => v.UserId == User.Id && v.CommentId == c.Id),
                               UserDisplayName = db.AspNetUserClaims.Any(cl => cl.ClaimType == "DisplayName" && cl.ClaimValue != "" && cl.User_Id == c.UserId) ? db.AspNetUserClaims.Where(cl => cl.ClaimType == "DisplayName" && cl.ClaimValue != "" && cl.User_Id == c.UserId).First().ClaimValue : db.AspNetUsers.Where(u => u.Id == c.UserId).First().UserName,
                               UserPhoto = db.AspNetUserClaims.Where(cl => cl.User_Id == c.UserId && cl.ClaimType == "Image").SingleOrDefault().ClaimValue,
                           }).OrderByDescending(o => o.Datetime).Skip(Start - 1).Take(Count).ToArray()
                       };
                }

and this is the Stack Trace:

[ArgumentNullException: Value cannot be null. Parameter name: value] System.Data.Linq.SqlClient.SqlJoin..ctor(SqlJoinType type, SqlSource left, SqlSource right, SqlExpression cond, Expression sourceExpression) +1222094 System.Data.Linq.SqlClient.Visitor.VisitMultiset(SqlSubSelect sms) +324 System.Data.Linq.SqlClient.SqlVisitor.VisitSubSelect(SqlSubSelect ss) +91 System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) +1014 System.Data.Linq.SqlClient.SqlVisitor.VisitExpression(SqlExpression exp) +15 System.Data.Linq.SqlClient.SqlVisitor.VisitNew(SqlNew sox) +186 System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) +1205 System.Data.Linq.SqlClient.SqlVisitor.VisitExpression(SqlExpression exp) +15 System.Data.Linq.SqlClient.Visitor.VisitSelect(SqlSelect select) +128 System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node) +1110 System.Data.Linq.SqlClient.SqlProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, ReadOnlyCollection`1 parentParameters, SqlNodeAnnotations annotations) +828 System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations) +279 System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Compile(Expression query) +104 System.Data.Linq.CompiledQuery.ExecuteQuery(DataContext context, Object[] args) +203 System.Data.Linq.CompiledQuery.Invoke(TArg0 arg0, TArg1 arg1, TArg2 arg2, TArg3 arg3, TArg4 arg4) +223 comments.Page_Load(Object sender, EventArgs e) in g:\Dropbox\Projects\NameBabies-New\comments.aspx.cs:21 System.Web.UI.Control.LoadRecursive() +71 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3178

Update:

This funny code solves the error but its not the right thing to do, can anyone suggest what do do instead?

public static Func<DatabaseDataContext, int, string, int, byte, NameCommentPageResult>
               GetNameComments = CompiledQuery.Compile((DatabaseDataContext db, int NameId, string UserId, int Start, byte Count)
          db.Names.Select(s  => new NameCommentPageResult
            {
                Count = db.NameComments.Count(q => q.NameId == NameId && q.VerifiedBy != "-1"),
                Name = db.Names.First(n => n.ID == NameId).Name1,
                Comments = db.NameComments.Where(c => c.NameId == NameId && c.VerifiedBy != "-1").Select(c => new NameCommentResult
                {
                    Datetime = c.Datetime,
                    Id = c.Id,
                    NameId = c.NameId,
                    UserId = c.UserId,
                    UserVoted = db.NameCommentVotes.Any(v => v.UserId == UserId && v.CommentId == c.Id),
                    UserDisplayName = db.AspNetUserClaims.Any(cl => cl.ClaimType == "DisplayName" && cl.ClaimValue != "" && cl.User_Id == c.UserId) ? db.AspNetUserClaims.Where(cl => cl.ClaimType == "DisplayName" && cl.ClaimValue != "" && cl.User_Id == c.UserId).First().ClaimValue : db.AspNetUsers.Where(u => u.Id == c.UserId).First().UserName,
                    UserPhoto = db.AspNetUserClaims.Where(cl => cl.User_Id == c.UserId && cl.ClaimType == "Image").SingleOrDefault().ClaimValue,
                }).OrderByDescending(o => o.Datetime).Skip(Start - 1).Take(Count).ToArray()
            }).First());

I edited => new NameCommentPageResult to db.Names.Select(s => new NameCommentPageResult and getting only the first result. please note that db.Names is irrelevant to my query and I can just use db.AnyTableName instead

nope, inner exeption is null and I don't know where this **parameter: value** comes from? — Ashkan Mobayen Khiabani, Jul 17 '15 at 13:02
@3-14159265358979323846264 I edited question and added the stack trace — Ashkan Mobayen Khiabani, Jul 17 '15 at 13:04
Is this relevant to your case? http://stackoverflow.com/questions/2407032/why-cant-you-return-a-list-from-a-compiled-query — 3-14159265358979323846264, Jul 17 '15 at 13:07
If you are unable to figure out where the error is coming from, you should try to debug it in smaller parts. For example, remove the `Count = db.NameComments...` line and see if the error goes away. If not, try the `Name = db.Names...` line and so on. — DavidG, Jul 17 '15 at 13:08
well I commented out many things, it works when I comment out all the `Comments = db.NameComments.Where(c => c.NameId == Id &&... ` but commenting out all its properties gives the same error, — Ashkan Mobayen Khiabani, Jul 17 '15 at 13:11
What is the reason for compile?As I remember EF does it Automatically. — slava, Jul 17 '15 at 13:14
I edited my code for testing and the error went away but its not the right thing to do, why is it like this? please see update section at the end of my question — Ashkan Mobayen Khiabani, Jul 17 '15 at 13:30

score 1 · Accepted Answer · answered Jul 17 '15 at 13:36

This is a L2S bug which is clear from the fact that internal L2S code is crashing in an uncontrolled way.

That said querying sub-collection (here: Comments) is either slow when it works (SELECT N+1) or is not supported. Probably, it's not supported in compiled queries. Compiled queries do not support certain constructs.

Probably, you should not query a sub-collection anyway because of the performance concerns. Rewrite your code so that this is not being done.

Since maybe you are learning .NET right now: LINQ to SQL is obsolete and ASPX pages are considered obsolete (or a technology of last resort) by many.

I separated the code into 2 queries and it works now. Thanks a lot — Ashkan Mobayen Khiabani, Jul 17 '15 at 13:48

Linq query does not work when compiled

1 Answers1