Linq randomly returns periodic null results.

Question

I've got a linq query, which is admittedly pretty large. I can't identify where it is going wrong because it ONLY happens on the remote server and I don't have the access to debug it. But it is basically like this...

This code lists all of the 'folders' that the current member can see. (Folders contain images.)

            if (membership == null || membership.Count() == 0)
                membership = new string[] { "Guests" };

            return DataContext.Folders.Where(f => f.Ring.Keys.Any(k => k.Tag.Id == id))
                .Where(i => i.Ring.Keys.Any(t => membership.Contains(t.Tag.Name))).OrderBy(n => n.Date).DefaultIfEmpty();

This code lists all of the 'tags' the user can see (tags are contained in rings, which can be hooked onto folders or images)

        IQueryable<Models.Tag> tags = null;
        do
        {
            DataContext = new Models.Contexts.DatabaseDataContext();

            tags = null;
            if (membership == null)
                membership = new string[] { "Guests" };

            tags = DataContext.Tags.Where(t => t.Keys.Any(k => k.Ring.Name == category))
                .Where(t => t.Keys.Any(k => k.Ring.Keys.Any(c => membership.Contains(c.Tag.Name)))).OrderBy(o => o.Name);
        }
        while (tags == null || tags.Count() == 0);

        return tags;

I enclosed it in a do loop, much to my dismay, to see if it could force it to actually keep calling until it was no longer null. No such luck, It still comes back empty. Again, this EXACT same code works 'sometimes', and 100% of the time on EVERY test back, querying the SAME database. I have even tried different web hosts - all with the same failures once it is live.

membership is a string[] that contains a list of all of the roles the member belongs to.

Folder
- Id
- RingId

Ring
- Id
- Name (Nullable)

Tag
- Id
- Name

Key
- Id
- RingId
- TagId

Image
- Id
- FolderId
- RingId

This is the basic database structure setup.

Now, this query runs fine on my local machine - in all of my tests, etc. But when I put it live, it just randomly starts returning null sometimes. I cannot find a rhyme or a reason to why, it runs fine for a few requests, then it just stops returning the results.

The result set it returns is pretty large. I'd say about 880 items. And the amount of times it is accessed per second is very, very high. I thought at first maybe it was just the stress of how many people were accessing it.

Is there any information I can provide that might help in debugging this? There are a few other queries similar to this one - I have tried, and tried, and tried, and I simply cannot reproduce the results in a debugger. I am getting all sorts of InvalidCastException calls - but there's never any casting. Everything is being returned as IQueryable - The views do not do anything special except take in Guids and pass the Membership data from the ASP.NET Membership Provider - which I've checked for and confirmed that it is working. (Inserted forced data, instead of letting the provider do it - still fails)

I will try to provide any information needed, but I am really getting frustrated - as none of this is making sense to me (why it would be failing).

Thank you very much for your time. (This is all done in .NET 3.5, ASP.NET MVC 1.0, C# ) - the objects were created using LINQ to SQL.

Answer 1

This really sounds like a load issue. Put some perf counters on the web server and watch it for usage.

Answer 2

Ok I'm not sure if this is your problem but it's too hard to write it back in a comment,

Your data context is disposable, assuming you're using LINQ to SQL. Therefore while you want to use it you can wrap it in a using statement like so:

    do
    {
        using (DataContext = new Models.Contexts.DatabaseDataContext())
        {

            tags = null;
            if (membership == null)
                membership = new string[] { "Guests" };

            tags = DataContext.Tags.Where(t => t.Keys.Any(k => k.Ring.Name == category))
                .Where(t => t.Keys.Any(k => k.Ring.Keys.Any(c => membership.Contains(c.Tag.Name)))).OrderBy(o => o.Name);
        }
    }

This way it will dispose your connection after every attempt to use it, otherwise it will remain in memory for some period of time. Because this period of time is up to the garbage collector, it could be the reason why it works some times and not others. Try it and let me know if it fixed your problem.