0

I have a few filters on my view, the first one is list by first name, last name and company name when one of these options are selected the user can then select a, b, c ... x, y, z to show only people starting with the selected letter.

if (collection["Filter"] == "2") {
    presentations = presentations.Where(x => x.Person.FirstName.StartsWith("A"));
    presentations = presentations.OrderBy(x => x.Person.FirstName);
}

Results returned are similar to 

John Squirel
Basil Boywiz
David Smith

This doesn't seem to work, what am I missing?

I dug a little further, this is the query causing the problem.

SELECT  [t0].[Description], [t0].[EventId], [t0].[Id], [t0].[PresentedOn], 
          [t0].[Slug], [t0].[SpeakerId], [t0].[Title], [t0].[Url]
FROM      [Presentations] AS t0
LEFT      OUTER JOIN [Speakers] AS t1 ON ([t1].[Id] = [t0].[Id])
WHERE    ([t1].[FirstName] LIKE 'B' + '%')
ORDER     BY [t1].[FirstName]
Daniel Draper
  • 259
  • 3
  • 15

2 Answers2

0

Ok after our long comments below, why don't you just chain the linq statements like below?

if (collection["Filter"] == "2") { 
   presentations = presentations.Where(x => x.Person.FirstName.StartsWith("A")).
       OrderBy(x => x.Person.FirstName);
}

Since the Where and the OrderBy are deferred until you actually do something with the query like a ToList(), try doing:

var orderedData = presentations.ToList();

Inspect it, it should be in the correct order as I can't see anything wrong with your linq other than the code you posted never actually is executed until you do a Select or ToList or something with it.

Kelsey
  • 47,246
  • 16
  • 124
  • 162
  • Presentation and Person are both classes that replicate the database table for the purpose of abstraction. – Daniel Draper Jul 16 '10 at 05:52
  • @Daniel Draper not LinqToSql classes I assume but something you filled? Compiles? What is the exact problem your having? Is it not coming out filtered or ordered correctly? – Kelsey Jul 16 '10 at 05:53
  • No they aren't LinqToSql classes, they are filled using SubSonic Simple Repository. They are ordered correctly but the StartsWith is the problem as you can see from the results above. – Daniel Draper Jul 16 '10 at 06:01
  • Sorry, it compiles and executes but returns invalid results. The type is IQueryable so .ToList() would cause a compiler error – Daniel Draper Jul 16 '10 at 06:20
  • @Daniel Draper I have updated my answer. Try it and see if it helps. It's getting late for me here so I will check back in the morning. – Kelsey Jul 16 '10 at 06:29
  • Marking as the answer as this should be working but it's not I will continue investigating. Thanks – Daniel Draper Jul 16 '10 at 06:56
  • @Daniel Draper based on your latest update, it looks like the ORM you are using doesn't create the join correctly so either your ORM is not defining the link from `Presentations` to `Speakers` correctly or your database is not setup correctly. I have no experience using SubSonic but if it works like LinqToSql then the problem is probably in your DB table relationship definition and SubSonic is just wiring it up as you have defined. – Kelsey Jul 16 '10 at 15:17
0

I have managed to resolve the problem. If you look at the query the line

LEFT OUTER JOIN [Speakers] AS t1 ON ([t1].[Id] = [t0].[Id])

should read

LEFT OUTER JOIN [Speakers] AS t1 ON ([t1].[Id] = [t0].[SpeakerId])

Not quite sure why this is happening though, can anyone see how to correct this problem?

Daniel Draper
  • 259
  • 3
  • 15