Filtering data from multiple tables in .net

Question

I have 4 tables in my database namely Restaurants,Cuisines,Facility,Dishes. I have created a table adapter that returns data from these tables. now i have to filter the data based on user selection for cuisine,facility or dishes and return the resultant restaurants. here is my query:

    public string alldata(string location, string cuisines, string facility)
    {
        location = location.Replace('|', ',');
        facility = facility.Replace('|', ',');
        cuisines = cuisines.Replace('|', ',');
        string loc; string data = null; int id;
        DataSet3.DataTable1DataTable all = getall.GetAllData();

        IEnumerable<DataRow> query = from resturants in all.AsEnumerable()

                                     where ((location.Contains(resturants.City)) && (facility.Contains(resturants.FacilityName) && (cuisines.Contains(resturants.Type))))
                                     select resturants;
        foreach (DataSet3.DataTable1Row item in query)
        {
            name = item.Name;
            loc = item.Location;

            id = item.RestaurantID;
            data += name + "%" + loc +"%"+id+ "?";
        }
        return data; 


    }

this query returns duplicate entries and it does not apply AND in selection. I am stuck. please help me.

score 0 · Answer 1 · answered Jan 23 '13 at 06:57

0

IEnumerable<DataRow> query =    (from resturants in all.AsEnumerable()

                                         where ((location.Contains(resturants.City)) && (facility.Contains(resturants.FacilityName) && (cuisines.Contains(resturants.Type))))
                                         select resturants).Distinct()

u can use the Distinct() operator to get distinct records in Linq

answered Jan 23 '13 at 06:57

Raghavan

637
3
12

IEnumerable query = (from resturants in all.AsEnumerable()where location.Contains(resturants.City) && facility.Contains(resturants.FacilityName) && cuisines.Contains(resturants.Type)).Distinct() ... trying ur statement with one parentheses less might help ? – Raghavan Jan 23 '13 at 07:06
no it is still not working. duplicate entries are not appearing but filtering is not correct as AND condition is not working properly – just a learner Jan 23 '13 at 07:29
AND is working now. just tell me how can i get the correct result when i pass more than one facility. if i pass two facilities, result should contain both facilities not just one. how can i do that? – just a learner Jan 23 '13 at 08:14
well u r trying to implement a IN SQL statement in LINQ. http://stackoverflow.com/questions/896123/linq-version-of-sql-in-statement .. that link explains IN statement.. http://wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql/ .. thats one more link.. cheers ! so it shud be var facilitynamesTags = {"A","B","C"} and in the LINQ wud be facilitynamesTags.contains(restaurant.facilitynames).. hope tht helps.. cheers – Raghavan Jan 23 '13 at 21:26

Filtering data from multiple tables in .net

1 Answers1