StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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 <em>entirely sure</em> I understand where you're running into difficulties, but I think the following should work:</p> <pre><code>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 }; </code></pre> <p>Let me know if there's something that I'm not understanding.</p> <h2>Update</h2> <p>I'm not sure why this isn't working for you. I just did the following in LINQPad (LINQ to SQL mode):</p> <pre><code>from p in person orderby p.clue_type.clue_type_id, p.clue_type.clue_type select new { p.person_id, p.clue_type } </code></pre> <p>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:</p> <pre><code>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] </code></pre> <p>Notice the LEFT <em>OUTER</em> JOIN. Will this not do what you're asking for?</p> <h2>Update 2</h2> <p>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:</p> <pre><code>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(); } </code></pre> <p>There are a few other ways to do something similar, but this gives you a general idea.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload