Which Data Access Model to use with ASP.Net MVC

[RichB]

I'm not sure if this is the right place to post this, but I would be grateful
for some advice on selecting a data access model for my MVC application.

I have tried using Linq to SQL and Entity Framework, both with mixed results.

Linq to SQL I probably can make work, though the adjustments I would need to
cater for EntitySets
(http://www.microsoft.com/communitie...19bb40bca7c0&lang=en&cr=US&sloc=en-us&m=1&p=1)
made me start looking at the Entity Framework. I cannot however get this to
work for creation of objects in the following scenario:

I have an activity which may occur at one or more locations on different
dates. I want to find all future activities (comprised of multiple objects)
and order them by the distance from my current location. I found this
article: http://blog.wekeroad.com/2007/08/30/linq-and-geocoding/ which I know
is referring to Linq to Sql, but I assume it also applies equally to Linq to
Entities. It basically concludes that Linq is unable to generate the SQL
required, and that a two stage process is required to get the data, then
filter by distance. I feel that the number of objects created relative to the
filtered number is too large to take this approach.

I’m also not sure that Linq to SQL is the best long term option as MS moves
effort away to EF.

I also understand that EF SP support is due to be improved in .NET 4.
However in the meantime what are my options?

Should I use Linq to SQL for this SP and EF for other actions.

OR is there another approach to SP with EF in my distance from scenario
which will allow me to create the objects for my List of Activities?

 

[Colbert Zhou [MSFT]]

Hello Richard,

Data Access Model is just the channel the Presentation layer go through to
communicate with the Data layer. So choosing which kind of Data Access
Model really depends on a very specified scenario or project requirement.
But based on my understanding, for the common scenarios, all of ADO.NET,
LINQtoSQL, Entity Framework should work fine. Just for a specified
scenario, one of them may be more appropriate.

The Entity Framework is the trend of Microsoft's Data Access Solution. It
consists of a good data model and a set of design-time and run-time to that
allow developers to describe the application data and interact with it at a
conceptual level. It is a good choice to adopt the Entity Framework if we
are using Database as data source. We can also find some useful samples
about using Entity Framework in ASP.NET MVC project from the internet,
http://code.msdn.microsoft.com/AspNetMvcAndEFSample

But I believe what you are interesting is not very tight to this
newsgroup's topic, ADO.NET, but more close to topic like, how to design an
datasource and consume it in your ASP.NET MVC application. My understanding
now is we want to store some activity information, like name, time,
interesting, location in the database or some source else. Then we want to
retreive these data and analyze to give a smart report for all activities
based on location distance, or insteresting preference. All of tranditional
ADO.NET, Linq to SQL, Entity Framework can help to retrieve the Activity
data from the database and construct the data as objects in memory. But if
the location is stored as Street(location detail) name. We should add our
own Business layer to manipulate the data we retrieved. The calculation of
distance for all activities should not be a DataAcessModel's task, but for
Business layer. And then we can filter the activities and display them in
the Presentation layer.

If you have questions or concerns regarding to the designations for
different layers in ASP.NET MVC, you can post in aspnet newsgroup. Experts
there should be able to give more helpful suggestions.

Have a nice day!

Best regards,
Ji Zhou
Microsoft Online Support Team

 

[Miha Markic]

Yep, just use the data access technology that suits you best.
The choice is unrelated to asp.net mvc...
And besides Linq to SQL and EF there are plenty of other ORMs out there.
I'd recommend checking out LLBLGenPro.

 

[RichB]

Thanks, what you say makes sense. I realise that there is not a huge amount
to go on in the information which I provided.

My concern with leaving the business layer to filter and order based on
distance could cause performance issues.

Say I have a paginated view or 20 Activities, but the request is for
activities occurring within the next 2 weeks. My total database response
could be say 60000 activities occurring nationally within the next week. So
in order to present the first 20 activities then I have to retrieve the 60000
activities and then sort them in the business layer. That sounds like a lot
of data to move around when I could instead ask the database to return just
the first 20.

Is there a point at which you would suggest a different approach, or is
processing the data on the business layer not going to have a significant
effect on performance? If there is a significant effect, then what other
options are available using ORM?

 

[RichB]

Thanks I'll take a look at LLBLGenPro. Alot of my issues are with getting to
grips with the technologies, and the MVC model binding doesn't seem to play
ball with EntitySets created by Linq to SQl.

I guess I need to get the technology which does suit me and then work out
the custom model binding in MVC.

Thanks for your response.
Richard

 

[Miha Markic]

Thanks I'll take a look at LLBLGenPro. Alot of my issues are with getting
to
grips with the technologies, and the MVC model binding doesn't seem to
play
ball with EntitySets created by Linq to SQl.

Not sure exactly what your problem is but try using .ToList() method on
retrieved data before binding.

I guess I need to get the technology which does suit me and then work out
the custom model binding in MVC.

The first makes sense anyway while the later might not be required.

 

[Miha Markic]

If you really don't have any special need then I'd fetch only the records
required (20 in your example) - which is (or should be) pretty
straightforward with any ORM.

 

[RichB]

Yes, that is my problem though what options do I have (In EF):

1. A Linq query is too "complicated" for the sql generation.
2. A Stored Procedure can only be mapped to a single entity.

Or is there an appropriate way to get around either of these issues I have
encountered?

 

[RichB]

Ok, how about this approach:

from occ in context.Occurrences
join i in db.NearestEvents().Skip(startingRecord).Take(20)
on occ.Id equals i.Id
select occ;

It seems to work, but what is it actually doing under the hood?
NearestEvents() is a table valued function returning the IDs of the
occurrence records that it finds (I've not actually incorporated the logic
for distance at present). Would this all get run on the DB? (As far as I can
tell in SQL Server Profiler it is running the following-
SELECT [t0].[Id].....FROM [dbo].[Occurrence] AS [t0]INNER JOIN ( SELECT
TOP (1) [t1].[Id] FROM [dbo].[NearestEvents]() AS [t1] ) AS [t2] ON
[t0].[Id] = [t2].[Id]

). I haven't tried with EF as I think that I'm going to stick down the Linq
to Sql route, but am I correct in assuming that it would work there too?

Finally in Linq to Sql, is it usual to create several linq-to-Sql models
dependent on the data required for each call?

Thanks, Richard

 

[Paul]

There is no replacement for creating your own DAL / Model .

 

[Miha Markic]

Ok, how about this approach:

from occ in context.Occurrences
join i in db.NearestEvents().Skip(startingRecord).Take(20)
on occ.Id equals i.Id
select occ;

It seems to work, but what is it actually doing under the hood?

Profiler or some other logging facitily will tell you if you are interested
(and it is a good thing to look under the hood).

NearestEvents() is a table valued function returning the IDs of the
occurrence records that it finds (I've not actually incorporated the logic
for distance at present). Would this all get run on the DB? (As far as I
can
tell in SQL Server Profiler it is running the following-
SELECT [t0].[Id].....FROM [dbo].[Occurrence] AS [t0]INNER JOIN ( SELECT
TOP (1) [t1].[Id] FROM [dbo].[NearestEvents]() AS [t1] ) AS [t2] ON
[t0].[Id] = [t2].[Id]

). I haven't tried with EF as I think that I'm going to stick down the
Linq
to Sql route, but am I correct in assuming that it would work there too?

There is no guarantee since every ORM might implement LINQ queries
differently or not at all. But I'd assume it would work.

Finally in Linq to Sql, is it usual to create several linq-to-Sql models
dependent on the data required for each call?

What do you mean by models?

 

[RichB]

Sorry I hadn't noticed your reply. By Models I probably mean contexts. So
for example I have a venues context for accessing data to do with venues, and
an activities context for accessing data associated to activities. I may also
have a activitiesSummary context for accessing a subset of the activities
data. Even though all of these tables are in the same database and
(especially since) they are dependent on each other.

I may be wrong, but my understanding is that if I have a single context
containing activities and venues ( each activity has a venue) then I cannot
get just the venue information without getting all associated Activites also.


For that reason I have seperate models/contexts (dbmls).