Linq or Stored proc - Which should I choose?

Question

An Activity occurs at a location on a specific date, but each activity may be repeated at the same or different locations on different dates. I have created an entity framework model, and wish to populate it with relevant Activities which occur between two dates, ordered by the locations distance from a specified location.

I therefore have a the following tables:

Activity (A)

Occurrence (O)

Location (L)

With relationships as follows:

A 1-n O n-1 L

I am having some difficulty in doing this using linq to entities, but am sure that I can achieve it in a stored procedure.

Is this something that I should be able to do using Linq or is it too complex a problem for Linq to create the Sql? Any pointers about writing the Linq query would be appreciated, if it is something that it should be able to do.

Answer 1

I don't see why that would be impossible to do, or even particularly difficult. Was that the question? :)

The activity table is not interesting to your query as I understand it, start by finding Occurrences. You can include the Activity data in the output like this:

dc.Occurrence.Include("Activity").Where(
    o => o.Date >= startDate && o.Date <= endDate
    && o.Location.DistanceFrom(someLocation) < maxDistance)

DistanceFrom would be however you determine the distance from a location.. I don't how anything about your database design.

If you're using SQL Server 2008 geocoding, I don't think that is supported yet. This article (and this continuation) might be of interest. It's about LINQ to SQL, but the expression building might be of help.

Answer 2

In Linq2Sql, the designer would automatically create relationships in the L2S classes (but would not display them in the designer)

In Linq2Entities, the designer will display foreign key relationships, but will not automatically create them -- that has to be done manually. (that statement was based on a beta of Linq2Entities --- it may no longer be true).

Answer 3

I have not used Linq, and maybe I am completely wrong but cant you use stored procedure even if you use Linq? ONE of the main reasons to use Stored procedure is to hide the underlaying datamodel from the business layer. By hiding the datamodel you can optimize the data access. The procedures is used as an interface to application and reports, and if you need to modify the data model this can be done without breaking the interface.

This is my opinion from a DBA perspective. As I said, I have no idea of what the puprose of Linq is.