R – LinqDataSource – how to select rows based on the current UserId

I'm used to using Linq to Entities in code, but I use LINQ to SQL often enough in LINQPad that I've become fairly familiar with it. I'm not entirely sure I understand where you're running into difficulties, but I think the following should work:

var sites = from s in DataContext.Sites
            orderby s.PrimaryAddress.AddressLine1,
                     s.PrimaryAddress.City,
                     s.PrimaryAddress.State,
                     s.PrimaryAddress.Country
            select new 
            {
                s.Id,
                s.SiteName,
                s.PrimaryAddress
            };

Let me know if there's something that I'm not understanding.

Update

I'm not sure why this isn't working for you. I just did the following in LINQPad (LINQ to SQL mode):

from p in person
orderby p.clue_type.clue_type_id,
        p.clue_type.clue_type
select new
{
    p.person_id, p.clue_type
}

The results all had clue_type = null. LINQ to SQL just treats null references as values with all-null properties. Here's the generated SQL:

SELECT TOP (10) [t0].[person_id], [t2].[test], [t2].[clue_type_id], [t2].[clue_type]
FROM [person] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[clue_type_id], [t1].[clue_type]
    FROM [clue_types] AS [t1]
    ) AS [t2] ON [t2].[clue_type_id] = [t0].[clue_type_id]
ORDER BY [t2].[clue_type_id], [t2].[clue_type]

Notice the LEFT OUTER JOIN. Will this not do what you're asking for?

Update 2

Making the query dynamic might be fairly difficult, depending on how dynamic you're making it. Here's one solution if you want to be able to order by any of the properties that you're returning, based on a string value that gets passed into your method:

public class SiteDisplayInfo
{
    public int Id {get;set;}
    public string SiteName {get;set;}
    public string PrimaryAddress {get;set;}

    public static readonly Dictionary<string, Func<IQueryable<Site>, IOrderedQueryable<Site>>> OrderByFuncs = 
    new Dictionary<string, Func<IQueryable<Site>, IOrderedQueryable<Site>>>
    {
        {"Id", q => q.OrderBy(s => s.Id)},
        {"SiteName", q => q.OrderBy(s => s.SiteName)},
        {"PrimaryAddress", 
        q => q.OrderBy(s => s.PrimaryAddress.AddressLine1)
                             .ThenBy(s => s.PrimaryAddress.City)}
    };
}

...

public IEnumerable<SiteDisplayInfo> GetSites(string orderByString)
{
    IQueryable<Site> sites = DataBase.Sites;
    if (orderByString != null && SiteDisplayInfo.OrderByFuncs.ContainsKey(orderByString))
    {
        sites = SiteDisplayInfo.OrderByFuncs[orderByString](sites);
    }
    var query = from s in sites
                select new SiteDisplayInfo
                {
                    Id = s.Id,
                    SiteName = s.SiteName,
                    PrimaryAddress = s.PrimaryAddress.AddressLine1 + s.PrimaryAddress.City
                };
    return query.ToList();
}

There are a few other ways to do something similar, but this gives you a general idea.

In the end I had to select all of the data (using the * option in the DataSource wizard), then remove the columns that I didn't want to display in the GridView. This might be a little inefficient, but it worked.