Linq to SQL - How to build up a where clause dynamically

[Sulphy]

Hi all,

I've been chipping away at this problem all day and I can't really make much progress. So thought I would post here to see if anyone had any ideas.

The problem
I'm building a search form to allow my users to search a staff directory. So users can search by staff number, first name or last name. So any mixture of these three fields (and possibly more in the future) are needed. I also need to allow partial matches to be found e.g. Searching Sm* would find Smith, Smooth, Smoke etc.

The code so far

Code: Select all

 TKDataContext db = new TKDataContext();

            Table<Staff_Table> _items = db.GetTable<Staff_Table>();

                     List<StaffDirectory> menulist = (from c in _items

                                             where c.UserId == varStaffNumber 
                                             select new StaffDirectory()
                                            
                                                    {
                                                        UserId = c.UserId,
                                                        FirstName = c.FirstName,
                                                        MiddleName = c.MiddleName,
                                                        LastName = c.LastName,
                                                        EMAILADDRESS = c.EMAILADDRESS,
                                                        COUNTRY = c.COUNTRY
                                                    }).ToList();

            return menulist;

In this working example I created it does return data but only when searching on the staff number. I'm not sure how to effectively build up a dynamic 'where' clause with Linq to SQL. I did initially try out building up a SQL style string e.g. WHERE c.UserID = 'XYZ' AND c.LastName Like '%sm%' - but passing as a variable in to the linq's where clause didn't work.

So I'm hoping there are some of you out there in the wider community who have already done what I'm trying to do and be able to share your insight and a clear example?

Thanks!

[Sulphy]

Just a quick update since my last post. I've managed to get what I was after... sort of!

I've tweaked the code to use the .contains syntax. When I do a search it's as if I've passed a search parameter as %smi% which will match all entries such as 'smith', 'smither' etc.

The only problem I have with this is that I want my end user to pass a % to declare when they want this partial matching to kick in. So a user might want sm%t or %th to match the above.

I google search seems to bring up the .Like syntax but I just can't get it to work.

Code: Select all

 var menulist = from c in _items
                                           select new StaffDirectory()
                                            
                                                  {
                                                      UserId = c.UserId,
                                                      FirstName = c.FirstName,
                                                      MiddleName = c.MiddleName,
                                                      LastName = c.LastName,
                                                      EMAILADDRESS = c.EMAILADDRESS,
                                                        COUNTRY = c.COUNTRY
                                                    };

                                             if (StaffNumber != null)
                                             {

                                                 menulist = menulist.Where(c => c.UserId.ToUpper().Contains( StaffNumber.ToUpper()));
                                             }

                                             if (FirstName != null)
                                             {
                                                 menulist = menulist.Where(c => c.FirstName.ToUpper().Contains( FirstName.ToUpper()));
                                             }

                                             if (LastName != null)
                                             {
                                                 menulist = menulist.Where(c => c.LastName.ToUpper().Contains( @LastName.ToUpper()));
                                             }

            return menulist.ToList();

[MariiaI]

If we correctly understood you, your end user passes the pattern for searching, something like "%sm%t%" or "sm%t". If so, we could recommend to use Devart.Data.Linq.SqlMethods.
Please refer here:
http://www.devart.com/linqconnect/docs/ ... thods.html
http://www.devart.com/linqconnect/docs/ ... ng%29.html

Try rewriting your query like this:

Code: Select all

menulist = menulist.Where(c => SqlMethods.Like(c.LastName.ToUpper(),pattern));

where 'pattern' - is the necessary search pattern, e.g. "%sm%t%" or "sm%t".

Please tell us if this helps.

[Sulphy]

Hi Mariial,

That did the trick! Thank you so much for taking the time to reply, your example was exactly what I needed )

Kind regards,

P