How can I search into database with WPF and Linq-to-entities model

Question

Hello,

I prepare a WPF project, where I want to implement a more complex search. I use LINQ to entities through the ADO.NET Entity model and plan to do the display in WPFToolkit DataGrid.

My search window should allow search by a few different criteria. My idea is to be able to write in (for example) name, surname and occupation textboxes in the application and receive a list of all people in the selected table that correspond to all 3 search parameters. I want to be able to search when all are entered or even if only one is entered.

I suppose that the other option is to bind the form to the database...still, I do not know how to search like this (I know how to display info, edit it and insert...but not search).

I though of applying a string to query mechanism, but in vain. Seems that it's not working. Please, share some ideas of how can I do this. Any source or code fragment will be appreciated.

Thank you!

Answer 1

You could create a search table in your database which will be filled by a trigger on normal entity objects. The search table could be: EntityTableName, EntityTableID, EntityDescriptionString. The entity description string would be something you generate on inserting the entity by combining relevant searchable fields. Then you would use a standard LIKE query in SQL, or .Contains in Linq2SQL.

Search is hard!

Answer 2

You could write a stored procedure in SQL to do this for you and then add it to your LINQ Entities.

The Stored Proc would take in your 3 parameters and then perform the query.

To handle cases where the value may be empty string (the user didn't type anything) you can add an or clause to filter it out.

SELECT Name, Surname, Occupation FROM tblWhatever
WHERE (@Name = '' OR @Name = Name)
AND (@Surname = '' OR @Surname = Surname)
AND (@Occupation = '' OR @Occupation = Occupation)

I haven't written SQL in a while, so that may not run right away. Hopefully you get the idea though.

Answer 3

Linq makes it really easy to compose queries so that you can build them up from simpler building blocks. There are also a wide range of levels you can go to for a system like this. You can have a system where the general form of the query is static, which is your standard Linq query, but you can go all the way to having a custom query description format that you then convert into a Linq statement.

It looks like in your case you have just a few possible filters. You can do it similar to this:

var Query = Context.MyDataSet; //Whatever is the standard base query

if (!string.IsNullOrEmpty(NameFilter))
    Query = Query.Where(e => e.Name.Contains(NameFilter));

if (!string.IsNullOrEmpty(SurnameFilter))
    Query = Query.Where(e => e.Surname.Contains(SurnameFilter));

...

var Result = Query.ToList();

As long as you have a fixed set of parameters users can search on, you can go pretty far with this method of building queries by optionally adding filters.

Answer 4

OK, here is the final solution tghat I got and it works.

var sQuery = from x in dataContext.Patients
             select x;
if (!string.IsNullOrEmpty(serName.Text))
    sQuery = sQuery.Where(x => x.Name.Contains(serName.Text));

if (!string.IsNullOrEmpty(serSurame.Text))
    sQuery = sQuery.Where(x => x.Surname.Contains(serSurame.Text));

Thanks a lot for the assistance!