linq-to-sql is this the most efficient code review

Question

hey all. Just wanting to know is this the most efficient way of getting values from a db: given;

-----------    ---------------    -------------
| Channel |    |Issue        |    |Topic      |
| path(ck)|    |channelID(fk)|    |issueID(fk)|
-----------    ---------------    -------------

• One channel has many Issues
• One Issue has many Topics
• path is an alternate key

I have written the following linq statment.

var content = (from c in db.Channels
where c.channel_holding_page == path
select new { c, _latestIssue = c.Issues.OrderBy(i => i.issue_created).Where(i => i.issue_isVisible == true).FirstOrDefault(), _topics = c.Issues.OrderBy(i => i.issue_created).Where(i => i.issue_isVisible == true).FirstOrDefault().Topics }).FirstOrDefault();

I want to get(working backwards here) all the topics associated with the latest issue(issue_created) that is set to be public(issue_isVisible) from said channel.

Is this the most efficient way or is there a method that would be quicker then this.

Answer 1

I think this is what your code is trying to accomplish:

     var channels = db.channels.Where (c => c.channel_holding_page == path);
     var issues = channels.Select (c => new { c, _latestIssue = c.Issues.Where (i => i.issue_isVisible).OrderBy (i => i.issue_created).FirstOrDefault () });
     var result = issues.Select (ci => new { ci.c, ci._latestIssue, ci._latestIssue.Topics }).FirstOrDefault ();

If there will be only one channel that will match the channel_holding_page check, then we can simplify this a bit more since the channels var can be flattened using a FirstOrDefault() right in the first line.

Answer 2

Hey,

Sometimes querying down works well, and you can drill up, as in:

from t in Topic
join i in issues
on t.IssueID equals i.IssueID
join c in channels
on i.ChannelID equals c.ChannelID
where c.Chanel_holding_path == path
select new
{
   i.issue_visible,
   c.channelid,
   t.topicID
}

Not exact,but you get the picture. You can drill up which makes it easier to work with objects.

Answer 3

I assume when u change places of .Where and .OrderBy it will speed the query up a bit.