posts - 82, comments - 0, trackbacks - 0

My Links



Post Categories

C# Download


Informativa cookie



Msdn Gallery



Wiki Tecknet

Search filters with dynamic LinqToSql

This example explains how to perform dynamic search filter with one or more conditions through the use of LinqToSql.

To try this example and nacessario have on the pc and sdk net.framework4.0 PowerPacks available here . 

Linq known as Language-Integrated Query and was introduced with the advent of VisualStudio 2008 and is an integral part in the NETFramework from version 3.5 to oggi.Linq and a technology with which it is possible to query in-memory objects, fields within the tables in a database, XML file, and much more simply and concisely, is divided into different DataProvider among which we LinqToObjects, LinqToXml, LinqToDataSet, ending with LinqToSql LinqToEntities EntityFramework.In used with this article we will see some of these operators also estensionMethod with which it can perform search queries on the tables named Person and Job Data Base within a type SqlCompact.Il project has a Class LinqToSql, with a file.dbml in the mapping of two tables as said precedenza.Vedremo Where the operator also called operator of restriction, which filters the data of a sequence on the basis of a predicate, which can be for example, select only the people living in a city or province, the operator Join , this operator combines the results according to a key value, for example, we can combine the data from one or more tables based on based on the name by the method Equals and retrieve information from one or more tables, the operator Distinct, this operator does is delete all the duplicate values ​​from a sequence, returns useful if we want a result of unique values ​​OrderBy operator, this operator reorders in increasing the query result according to a predicate, for example a list of people ordinara based on their surname, then the operator ToList, this operator converts the query result in a list type System.Collection.Generics.List <t>. The example also has a main form where you can create a Database , connect and do the insert, modify and delete data from tables, a form of selection based on the state and sixteen different tasks, this form contains the Linq query with which you executed the search filter, and finally the Dynamic Forms the preview of the search result and then with which can print the contents of research performed in the following part of the code precedenza.Di more interesting, namely that of the Class search containing the search filter dynamically.

        //Click event of btnFind
        private void btnFind_Click(object sender, RoutedEventArgs e)
            /*This Linq query performs a search based on what the user selects the Search form,
             * the form includes three RadioButton and CheckBox controls sixteen,
             * first of all check if the user has selected at least one RadioButton and one or more checkboxes,
             * otherwise it will be perceived from a messagebox.
             * Next check out what has been selected, this is by LinqToObjects query,
             * first retrieves the name of the selected RadioButton control and the second the name or names of the selected checkboxes,
             * which is becoming the operator and Where the predicate inside.

             *This query and the search for or interested in the tasks assigned to each person.

                         var result = ctx.GetTable <Job> ()
                         . Where (w => w.ACTIVITY.Equals (_activity.Content.ToString (). ToUpper ()) && w.STATE.Equals (_state.ToUpper ()))
                         . Join (ctx.PERSON, job => job.ID, cust => cust.ID, (job, name) => new {name.NAME, name.SURNAME, name.ADDRESS, name.ZIPCODE, name.CITY} )
                         . OrderBy (a => a.SURNAME)
                         . Distinct ()
                         . ToList ();

             *A check is performed on the Job table if there are people who work a year and a corresponding duty,
             *will join the two tables placed in the Job and person using the ID field will be extracted and all the information
             *of the person or persons who fulfill the conditions specified in Where previous predicate operator,
             *then using the OrderBy operator will be sorted in ascending according to the specified predicate,
             *in this case by name, will be eliminated any double occurrences, and this task Distinct operator,
             *he will remove all occurrences double and the method ToList convert the result of the query
             *that will anominustype type in a list and we'll look at why
            if (Validations.ChechBoxAndRadioButtonChecked(dgvSearch) < 2)

            var print = new PreviewDialog();
            var _state = string.Empty;

            using (var ctx = new ContactDataContext(Properties.Settings.Default.path))
                foreach (var myRadioButton in dgvSearch.Children.OfType<RadioButton>().Where(myRadioButton => myRadioButton.IsChecked.Equals(true)))
                   _state = myRadioButton.Content.ToString();

                foreach (var myCheckBox in dgvSearch.Children.OfType<CheckBox>().Where(myCheckBox => myCheckBox.IsChecked.Equals(true)))
                    var _activity = myCheckBox;
                    var result =  ctx.GetTable<JOB>()
                        .Where(w =>w.ACTIVITY.Equals(_activity.Content.ToString().ToUpper()) && w.STATE.Equals(_state.ToUpper()))
                        .Join( ctx.PERSON, job => job.ID, cust => cust.ID,(job, name) => new  {name.NAME, name.SURNAME, name.ADDRESS, name.ZIPCODE, name.CITY })
                        .OrderBy(a => a.SURNAME)

                    foreach (var item in result)
                        /*The method of the class LoadData PreviewDialog populates a list of such Persons passing all the data found by the search query,
                         * which is why we converted the result of the query in a list, in the class PreviewDialog will find the list of
                         * such person and the method LoadData
                        print.LoadData(item.NAME, item.SURNAME, item.ADDRESS, item.ZIPCODE, item.CITY);

The full sample with code sample is avialible here.


Print | posted on sabato 25 agosto 2012 01:30 | Filed Under [ Articles C# in English ]


No comments posted yet.

Post Comment

Please add 7 and 4 and type the answer here:

Powered by:
Powered By Subtext Powered By ASP.NET