How can I combine these linq queries into one?

Question

Being new to LINQ, I created a couple queries and would like to combine them into one, but I am not sure how to do it. Here they are:

var u = dc.Users.Where(w => w.UserName == userName).SingleOrDefault();

var m = dc.Memberships.Where(w => w.UserId == u.UserId).SingleOrDefault();

m.PasswordQuestion = securityQuestion;
m.PasswordAnswer = securityAnswer;

dc.SubmitChanges();

dc.Users is the aspnet_Users table
dc.Membership is the aspnet_Membership table

Also, What is the difference between SingleOrDefault and FirstOrDefault?

Answer 1

SingleOrDefault will throw an error if your sequence contains more than one element, FirstOrDefault does not. In this case, you'd probably want FirstOrDefault, because users can have more than one membership.

var m = dc.MemberShips where(w => w.UserId.UserName == userName).FirstOrDefault();

Answer 2

SingleOrDefault means "if there are no elements, give me the default, if there is one, give me it, otherwise, throw an exception". FirstOrDefault means "if there are no elements then give me the default, otherwise give me the first one".

As for your question about combining the queries -- why would you? If the code is working well as written, why change it?

To answer a question you didn't ask: the usual way of combining queries is to use a query continuation. A common pattern is:

var q1 = from y in something 
         somequeryclauses;
var q2 = from x in q1 
         someotherquerqyclauses;

You could write this as one big query like this:

var q2 = from x in (from y in something 
                    somequeryclauses) 
         someotherqueryclauses;

which gets hard to read. You can combine the two queries with a query continuation:

var q2 = from y in something 
         somequeryclauses 
         into x
         someotherqueryclauses;

Make sense?

Answer 3

I don't have Visual Studio in front of me, but it would be something like this:

var m = dc.Memberships.Where(w => w.Users.UserName == userName).SingleOrDefault(); 

m.PasswordQuestion = securityQuestion; 
m.PasswordAnswer = securityAnswer; 

dc.SubmitChanges(); 

w.Users allows you to follow the foreign key link between the membership and users tables.

SingleOrDefault will throw an exception if more than one result is returned. FirstOrDefault returns the first record only.

Answer 4

Not sure if they have a relationship (they should). If they do, then the linq-to-sql designer will give you a User property on the membership object (or the other way around). Then you can write something like this:

var membership = dc.Memberships.Where(x => x.User.UserName == userName).SingleOrDefault();

If they don't have a relationship you can write something like this:

var membership = (from m in dc.Membership join u in dc.Users on u.UserId equals m.UserId
                  where u.UserName == userName
                  select u).SingleOrDefault();

The difference between SingleOrDefault() and FirstOrDefault() is that SingleOrDefault() assumes that there is no more then one item that matches the query. If two items match the query then a exception will be thrown. While if you use FirstOrDefault() and there is two items that match the query then the first one will be selected.