Linq vs Stored proc - which should you choose?
Activity takes place in a location on a specific date, but each action can be repeated in the same or different locations on different dates. I have created an entity framework model and want to populate it with the appropriate actions that occur between two dates, ordered by distance from a specified location.
Therefore, I have the following tables:
Action (A)
Occurrence (O)
Location (L)
With relationships like this:
A 1-n O n-1 L
I am having difficulty with this using linq for entities, but I am sure I can achieve this in a stored procedure.
Is this something I would have to do with Linq, or is it too difficult a problem for Linq to create Sql? Any pointers to writing a Linq query would be appreciated if that's what it should do.
source to share
var query = from occurrence in occurrenceList
join activity in activityList on occurrence.ActivityID equals activity.ID
join location in locationList on occurrence.LocationID equals location.ID
let distance = CLocation.Distance (referenceLocation, location)
orderby distance, activity.Name
where start <= occurrence.Date && occurrence.Date <= end
select new
{
ActivityName = activity.Name,
LocationName = location.Name,
Distance = distance,
Date = occurrence.Date
};
source to share
I do not understand why this is impossible or even particularly difficult. It was a question?:)
The action table is not interesting to your query as I understand it, starting with the search for Occurrences. You can include data Activity
in the output like this:
dc.Occurrence.Include("Activity").Where(
o => o.Date >= startDate && o.Date <= endDate
&& o.Location.DistanceFrom(someLocation) < maxDistance)
DistanceFrom
will, however, you determine the distance from the location. I am not doing anything about your database design.
If you are using SQL Server 2008 geocoding, I don't think this is supported. This article (and this sequel ) may be of interest. This is about LINQ to SQL, but creating expressions can help.
source to share
I haven't used Linq and maybe I'm completely wrong, but can't use a stored procedure even if you are using Linq? One of the main reasons for using Stored Procedure is to hide the database subclass from the business layer. By hiding data, you can optimize data access. Procedures are used as an interface for applications and reports, and if you need to change the data model, you can do so without breaking the interface.
This is my opinion from a DBA point of view. As I said, I have no idea what Link's puppy is.
source to share
In Linq2Sql, the designer automatically created relationships in the L2S classes (but did not display them in the designer)
In Linq2Entities, the designer will display foreign key relationships, but will not automatically create them - this must be done manually. (this statement was based on the beta version of Linq2Entities --- it can no longer be true).
source to share