No rows returned using a dataset with a variable where statement

Question

Hi,

Is it possible to populate a variable using a dataset with a variable 'where' clause?

I need to populate a variable from a dataset. The dataset consists of a SQL table entity with an additional 'where' statement
which is built using a string variable (q)

The statement is:

var items1 = from M_P_H_R qItems in this.DataWorkspace.TESTDBData1.M_P_H_Rs + q select qItems;

For Example: q is " where Start_Date = 01/01/2010"
(Start_Date is a SQL smalldatetime column).

NOTE: The q variable is dynamic and could contain 1 or more columns/values

I am not getting rows returned.

Any ideas please?

Thanks in advance

Answer 1

I have found a way of including the 'dynamic where' statement:

var items1 = from M_P_H_R qItems in this.DataWorkspace.TESTDBData1.M_P_H_Rs select qItems + q;

However, next I need to loop through each of the records and have found that ALL records are being returned
despite using the above where statement filter.

this.Save();
foreach (var qItems in items1)
                {
                    M_P_H_R mp = new M_P_H_R();   //Create a new instance to add each record
                    {
                        mp.Item = qItems.Item;
                        mp.Destination = qItems.Destination;
                        mp.Start_Date = qItems.Start_Date;
                        mp.End_Date = qItems.End_Date;
                        mp.Cost = qItems.Cost;
                        mp.Source = qItems.Source;
                        mp.User_Name_Amendment = qItems.User_Name_Amendment;
                        mp.Date_Of_Amendment = qItems.Date_Of_Amendment;
                    };
                    i = i + 1;
                    this.Save();
                }

 

Any Ideas? 

Answer 2

It would help if you give us the actual query for:

var items1 = from M_P_H_R qItems in this.DataWorkspace.TESTDBData1.M_P_H_Rs select qItems + q;

instead of describing what "q" is. There's probably something wrong with the query such that the effective constraints evaluate to "just return everything".

---

Justin Anderson, LightSwitch Development Team

Answer 3

You can't just add a string to your collection in a linq query and expect that a Where clause in the string will actually restrict the selection. Linq just doesn't work like that.

See http://social.msdn.microsoft.com/Forums/en-US/lightswitch/thread/de12a97c-9c68-4de4-89fc-40a607da551d

and http://social.msdn.microsoft.com/Forums/en-US/lightswitch/thread/d37dbeb5-82d4-4f67-8715-cc314486e836

for proper ways to do dynamic queries.

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.