CODEWITHSUNDEEP
c#asp.netasp.net-mvclinqlinq-to-sql
Muhammad Adeel Zahid
Muhammad Adeel Zahid

Reputation: 17784

Sequence contains no elements Linq-to-Sql

I'm having very strange problem in this simple linq query

return (from doc in db.umDocumentActions
                    where doc.sysDocument.ModuleID == modid
                    join roleaction in db.umRoleActions on doc.DocumentActionID equals roleaction.DocumentActionID
                    where roleaction.RoleID == roleID
                    select new { doc.DocumentID, roleaction.RoleID }).Count() > 0;

When this query is called it gives invalid operation exception telling me that sequence contains no elements. It happens when there is fair amount of traffic on the site. I am using following static method to get instance of datacontext.

public static EvoletDataContext Get() 
        {
            var connection = ProfiledDbConnection.Get(new SqlConnection(ConfigurationManager.ConnectionStrings["cnstring"].ToString()));

            return new EvoletDataContext(connection);
            //return DataContextUtils.CreateDataContext<EvoletDataContext>(connection);
        }

I'm afraid that this method is creating problem as static methods are not thread safe. Any views?

Upvotes: 0

Views: 1552

Answers (1)

Doctor Jones
Doctor Jones

Reputation: 21664

My best guess would be that sysDocument is actually a seperate table with a reference to DocumentID. This would normally mean that there would be a related collection of sysDocuments in the document class, but I'm guessing that you've changed the cardinality to "one to one" in the Linq to SQL designer.

When using a "one to one" cardinality, Linq to SQL uses the Single() method behind the scenes to get the sysDocument. This means that if there are no related sysDocuments you will get an invalidoperation exception.

You can fix this by wither changing the cardinality in you Linq model from "one to one" to "one to many" and use the SingleOrDefault() method to get the related sysDocument from the sysDocuments collection.

If that doesn't sound appealing you can look in the database to find which document doesn't have a related sysDocument and fix it manually.

UPDATE:

Instead of basing the query off the documentActions, try basing it off the sysDocument table instead. I've had to guess at what the table will be called so this might not compile, but hopefully you get the idea:

var query = from sysDocument in db.sysDocuments
            where sysDocument.ModuleID == modid
            let doc = sysDocument.umDocumentAction
            join roleaction in db.umRoleActions on doc.DocumentActionID equals roleaction.DocumentActionID
            where roleaction.RoleID == roleID
            select new { doc.DocumentID, roleaction.RoleID };

//return true if there are any results (this is more efficient than Count() > 0)
return query.Any();

Upvotes: 1

Related Questions