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.

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, 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?

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?