So you can probably tell that I've been doing a lot of LINQ lately. :) One thing I've found is that it's easier for me to write complex queries in the database as stored procedures or views, and then use Linq to SQL to retrieve the results, sort, filter, do paging, etc.

The downside of that approach is the results normally come back as a <view or proc name>Result type, instead of the actual table type. That's sometimes an issue, because I use partial classes to give my table classes some extra functionality, and I might want to allow updates, etc. against various tables.

So the goal is to be able to call a view or stored procedure, but coerce those results into a good ol' MyTable object.

You may already know how you can drag a stored procedure or view from your database (in Server Explorer), and drop it on top of a Table entity in the O/R designer. That will cause the stored procedure to return results of that table's type, instead of <procname>Result.

But Rick Strahl had a great post from a while back explaining how you can use views, procs, & dynamic SQL, and cast those results to a specific table type using ExecuteQuery. A snippet from Rick's post is below:

What's also interesting is that when you provide LINQ a query like this it still works with the DataContext's change tracking. For example, the following code actually works as you'd expect:

IEnumerable<Customer> custList =  context11.ExecuteQuery<Customer>("select * from Customers where CustomerId={0}", "ALFKI");
Customer cust11 = custList.Single<Customer>();
Response.Write(cust11.CompanyName); 
cust11.CompanyName = "Alfreds Futterkiste " + DateTime.Now.ToString();
context11.SubmitChanges(); 

custList = context11.ExecuteQuery<Customer>("select * from Customers where CustomerId={0}", "ALFKI");
cust11 = custList.Single<Customer>();
Response.Write(cust11.CompanyName);

The cool thing (as Rick mentions) is that you don't have to do a "select * from Customers" -- you could do a "select CompanyName, ContactName from Customers" and it will work, too. You don't need to bring back all columns in order to successfully cast the result to a Customer object.

I'll post my continuing forays into Linq as I continue. One thing that remains to be seen is whether Linq to SQL is robust enough to be helpful vs requiring me to extend it too much to do what I want/need.

I wanted to put up a few examples of SQL vs Linq to SQL for my future reference, since we're using it in one of our social media projects for artists. I'm pretty handy at SQL, but it doesn't translate exactly to Linq to SQL.

Example 1

Assume you have a view called vev_bws_mediaVoteDownloadHistory that votes and downloads for different media by date. So it contains columns like mediaId, artistId, activityDate, votes, and downloads.

If we wanted to query this view to get the total number of votes and downloads for a given media, you could use SQL like this

SELECT 
    votes = SUM(votes),
    downloads = SUM(downloads)
FROM vev_bws_mediaVoteDownloadHistory
WHERE mediaId = 12345

veDataContext dc = new veDataContext();

var totals = (from v in dc.vev_bws_mediaVoteDownloadHistories
              where v.mediaId.Equals(12345)
              group v by v.mediaId into h
              select new
              {
                  votes = h.Sum(x => x.votes),
                  downloads = h.Sum(x => x.downloads)
              }).FirstOrDefault();

if (totals == null) then return; // no totals

Response.Write(totals.downloads);
Response.Write(totals.votes);

Example 2

So the first example was jsut getting a few simple sums. Here's a bit more complex example.

Assume you have a view called vev_bws_userMediaHistory that logs votes for a given artist and his/her media. The view fields like votes, artistName, mediaName, userId (the user who cast the vote), and voteDate (a datetime column storing the date & time of a vote).

So since voteDate contains dates and times, I want to display all the votes grouped by media, artist, and date (not time). I could use SQL like this:

SELECT 
    votesperday = SUM(votes), 
    date = CAST(FLOOR(CAST(voteDate as FLOAT)) AS DATETIME),
    mediaName,
    artistName
FROM vev_bws_userMediaHistory
WHERE userId=1
GROUP BY 
    CAST(FLOOR(CAST(voteDate as FLOAT)) AS DATETIME),
    mediaName,
    artistName
ORDER BY CAST(FLOOR(CAST(voteDate as FLOAT)) AS DATETIME) DESC

veDataContext dc = new veDataContext();

var votes = (from v in dc.vev_bws_userMediaHistories
             where v.userId.Equals(1)
             group v by new 
             { 
                 v.voteDate.Date, 
                 v.artistName, 
                 v.mediaName
             } 
             into h
             orderby h.Key.Date descending
             select new
             {
                 date = h.Key.Date,
                 artistName = h.Key.artistName,
                 mediaName = h.Key.mediaName,
                 votesperday = h.Sum(x => x.votes)
             });

select new
{
    h.Key,
    votesperday = h.Sum(x => x.votes)
}

I would need to use something like <%# Eval("Key.artistName") %> instead.

FYI, there's a bug in the RTM LinqDataSource where child tables aren't loaded unless you have updating or deleting enabled. Apparently if you have a LinqDataSource that doesn't have updates or deleted enabled, ObjectTracking is turned off (for performance reason), but deferred queries (e.g. queries to pull back child rows) aren't executed.

So statements like

<%# Eval("ChildTable.ChildTableField") %>

in your ListView, etc. won't work.

Annoying, needless to say. But at least now I know.

So, there are a few options:

1. Set EnableUpdate="true", which will enable ObjectTracking and cause deferred updates to work
2. Add a SELECT statement in your LinqDataSource (e.g. SELECT="new (field1, field2, childTable)") to pull back everything you need.
3. Handle the ContextCreated event to either manually set ObjectTrackingEnabled, or manually set LoadOptions.

public void OnContextCreated(object sender, LinqDataSourceContextEventArgs e) {
  ((DataContext)e.ObjectInstance).ObjectTrackingEnabled = true;
} 

OR 

public void OnContextCreated(object sender, LinqDataSourceContextEventArgs e) {
    var dataLoadOptions = new DataLoadOptions();
    dataLoadOptions.LoadWith<MyTable>(t => t.ChildTable);
    ((DataContext)e.ObjectInstance).LoadOptions = dataLoadOptions;
}

Setting LoadOptions is normally the best approach for performance.