linq-to-sql is this the most efficient code review
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 t开发者_如何学运维he 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.
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.
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.
I assume when u change places of .Where and .OrderBy it will speed the query up a bit.
精彩评论