Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
In the DataSets days, I had a form with a datagrid view, and the user could enter a text in a single textbox as a filter. The program would then filter the data in the datagridView like so:
 
ContactBindingsource.Filter = String.Format("name like {'%0%'} or occupation Like {'%0%'}", Me.filterTextbox.Text)
 
I think the Filter method on a bindingsource doesn't work in EF.
What is the best practice in EF4 to do a similar thing? Do I need Entity SQL instead of LINQ to entities?
 
Thank you,
A.J. Nijenhuis
Posted 14-Mar-11 4:55am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I`ll construct the query dinamycally depending on the parameters given.
Example, a datagrid and filters like name and country:
var objSet = objContext.EntityName //returns IQueryable and over this object we add the filters
if(!string.IsNullorEmpty(txtName.Text))
{
   objSet = objSet.Where(x => x.Name.Contains(txtName.Text));
}
if(ddlCountry.SelectedValue != -1)
{
   objSet = objSet.Where(x => x.Country == ddlCountry.SelectedValue );
}
return objSet.ToList();
 
As the query has not been executed until we call ToList(), you can programmatically add where conditions to it and at the end it will construct the query only with the passed parameters. Look that I´m using string.Contains in place of the "like "%{0}%"" as we used to. I haven't seen how this is translated at the database but it has worked for me!
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Interesting approach, however this seems to be throwing an invalid cast exception, which says that an ObjectQuery cannot be cast to an ObjectSet:
 
System.InvalidCastException occurred
Message=Unable to cast object of type 'System.Data.Objects.ObjectQuery`1[PrismaModel.Aanvraag]' to type 'System.Data.Objects.ObjectSet`1[PrismaModel.Aanvraag]'.
Source=PrismaModel
StackTrace:
at PrismaModel.AanvraagManager.GetAanvragen(String zoektekst)
InnerException:
 
In the mean time, I have successfully tried a dynamic query like so:
 
Dim ctx As PrismaEntities = ConnectionManager.CreateContext
 
Dim filter As String = "1=1"
 
filter &= String.Format(" AND it.klant.realtie.naam LIKE '%{0}%')", zoektekst)
 
If IsNumeric(zoektekst) Then
  filter &= String.Format(" AND it.aanvraagnr = '{0}')", zoektekst)
End If
 
Dim q = ctx.aanvragen.Where(filter).OrderBy("it.aanvraagDatum DESC, it.Artikelnr")
 
Return New SortableBindingList(Of Aanvraag)(q.ToList)
 
Cons: the filter is not strongly typed Frown | :(
Pro's: descending ordering supported in one combined order statement.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 645
1 OriginalGriff 453
2 CPallini 239
3 CHill60 180
4 Peter Leow 175
0 OriginalGriff 6,092
1 Sergey Alexandrovich Kryukov 4,898
2 Maciej Los 3,159
3 Peter Leow 3,129
4 DamithSL 2,490


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 21 Feb 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100