CODEWITHSUNDEEP
c#linq-to-sqlasp.net-mvc-3
swasheck
swasheck

Reputation: 4703

LINQ to Sql is empty and throws Object reference not set to an instance of an object. error

I use the following query to get a result set

var overlaps = from s in db.signups
               join u in db.users on new { userid = s.userid } equals new     { userid = u.studentid }
               join a in db.activities on new { activityid = s.activityid } equals new { activityid = a.id }
               where
                    s.userid != Convert.ToInt32(Request.Cookies["studentid"].Value) &&
                      (from signups in db.signups
                         where
                               signups.userid == Convert.ToInt32(Request.Cookies["studentid"].Value)
                             select new
                             {
                                 signups.activityid
                             }).Contains(new { s.activityid })
                        orderby
                          u.studentid
                        select new
                        {
                            a.name,
                            u.firstname,
                            u.lastname,
                            u.studentid,
                            u.email
                        };

I'm pretty new to LINQ so I actually wrote the Sql and then used Linqer to generate the LINQ, so if this can be done more efficiently then please let me know. Having said that, this is not the problem.

The problem is that when I do

foreach(var overlap in overlaps)
{
    //do something
}

it throws the object reference not set error. This is being run in an MVC 3 application.

However, when this is run in a Console application, it runs without issue; it just returns no results. I've tried using DefaultIfEmpty but just can't find anything that addresses how to use this with anonymous types.

So ... is my approach correct? If not, what should I do differently?

Thanks, in advance.

Upvotes: 1

Views: 1744

Answers (4)

Rikon
Rikon

Reputation: 2706

To your first question, you are appropriately worried about how messy the linq is... we often will take messy linq and just do a dataContext.ExecuteQuery or .ExecuteCommand because one of linq's major short falls is their ability to optimize complex queries as well as you could.

To get an idea of how badly linq has botched your query there, run it through the query analyzer and compare it to what you started with... My guess is that it will be comical!

Upvotes: 1

asawyer
asawyer

Reputation: 17818

I don't know if this is your problem, but your join syntax is really weird.

You don't have to build anonymous types here, just compare directly.

join u in db.users on s.userid equals u.studentid
join a in db.activities on s.activityid equals a.id

Same with this:

select new
{
    signups.activityid
}).Contains(new { s.activityid })

Can be just:

select signups.activityid).Contains(s.activityid)

And why in the world do you want to redo all the work to convert the cookie parameter to an int over and over?

var studentId = Convert.ToInt32(Request.Cookies["studentid"].Value);
//use this instead now in the query, dont repeat yourself

Upvotes: 2

James Curran
James Curran

Reputation: 103605

Well, first off, I'm pretty sure you can simplify the first few lines down to :

from s in db.signups
join u in db.users on s.userid equals u.studentid 
join a in db.activities on s.activityid equals a.id

in fact, if you've defined foreign keys on those properties, you don't need the joins at all -- LINQ will handle them for you automatically: Write s.User.firstname instead of u.firstname etc.

As for your main problem, check all the component of that query, mainly "db" and "Request" (and how exactly does Request.Cookies work in a console application?)

Upvotes: 0

Chad La Guardia
Chad La Guardia

Reputation: 5174

ICBW, but I would try casting overlaps, something like:

foreach(OverlapType overlap in overlaps as IEnumerable<OverlapType>)
{ 
    //stuff
}

This of course means you will need to make a model of the object you are getting from the database. But really, you should have one anyway, that is the whole premise behind MVC (Model View Controller)

Upvotes: 0

Related Questions