LINQ Union Column If Authenticated

Question

I have a simple rating application of an Item. There are three main tables:

Item
{
   ItemID,
   Contents
}

asp_User
{
   UserID,
   Name
}

Rating
{  
   RatingID,
   ItemID,
   UserID,
   int Rating
}

I have linq code that reads the items:

var q = from item db.Item
        select item;

I would then like to append to q a column that contains the rating for each item row of the currently authenticated user. If the user is not logged in or there is no rating provided by the authenticated user, the result will be 0.

I'm using the SqlMembershipProvider if that matters.

Example

The final result of q should look something like this:

[Authenticated]

//The currently authenticated user has commented on Item.ID = 1 and has given it a 9.
q = {ID = 1, Contents = "Whatever", Rating = 9},

//The currently Authenticated user has not commented on Item.ID = 2.
{ID = 2, Contents = "Something", Rating = 0};

[Not Authenticated]

//There is not an authenticated user
q = {ID = 1, Contents = "Whatever", Rating = 0},

//There is not an authenticated user
{ID = 2, Contents = "Something", Rating = 0};

Answer 1

What do you intend to do with "var q"? It's hard to tell what your intentions are from the question, and in this case it matters a lot. Here is the best I can do based on the lack of context. Hopefully this will help:

if(User.IsAuthenticated)
{
    var q = from item in db.Item
            join r in db.Rating on c.Id equals r.ItemId
            select new {ID = item.Id, Contents = item.Contents, Rating = r.Rating};

    // Do whatever you want with this here.
}
else
{
    var y = db.Item;

    // Do whatever you want with this here.
}

Answer 2

From a performance perspective Ocelots solution is the way to go (especially if you query multiple items). But to satisfy the condition that Rating should be a property of Item, you could extend the Item class by using a partial class within the same namespace of your LINQ-to-SQL data classes:

public partial class Item
{
    public int Rating
    {
        get
        {
            if (!Thread.CurrentPrincipal.Identity.IsAuthenticated)
                return 0;

            using (var db = new ApplicationDataContext())
            {
                return db.Item
                    .Where(r => r.ItemID == this.ItemID
                        && r.UserID == Thread.CurrentPrincipal.Identity.Name)
                    .Select(r => r.Rating)
                    .SingleOrDefault();
            }
        }
    }
}

If you want to get the same results with a single SQL request like Ocelot proposed, you should rather use GroupJoin (works like a SQL LEFT OUTER JOIN) and select the current users rating as I do above.