Linq query where clause

Question

I am using Linq to Entities and I have a query something like this

context.Hotels
        .Where(h => h.HotelType.Contains(s.HotelTypeId.ToString()))
        .Select(hotel => new Model.Hotel
           {
              HotelId = hotel.HotelID,
              HotelName = hotel.HotelName,
              HotelFileName = hotel.HotelFileName,
              StarRating = hotel.StarRating,
              CountryName = hotel.Country.CountryName,
              PlaceName = hotel.Place.PlaceName
           })

I am using .ToString() in where clause which I know is not valid when work with Linq To Entities. But actually "HotelType" column have values separated with pipe characters like 1|2|3..Now I want to extract only those Hotels that have a type 1..How is it possible? Please help

First of all, you must change your tables structure. It is not good to save 1|2|3 in one cell. You can create another table with the name HotelTypes with at least two columns: Hotel_ID and Type_ID. Then use join to this table. — Farhad Jabiyev
– Farhad Jabiyev, Commented May 10, 2014 at 11:05
I have a very large database, it is quite difficult at this moment. I am wondering if somehow i can make it possible — Ammar Khan
– Ammar Khan, Commented May 10, 2014 at 11:06
Consider making the question title more specific. This one is really not describing your problem at all. — Ondrej Janacek
– Ondrej Janacek, Commented May 10, 2014 at 11:06

Daniel Brückner · Accepted Answer · 2014-05-10 11:19:07Z

3

It's a hack but it should work.

Where(h => ("|" + h.HotelType + "|").Contains("|" + s.HotelTypeId.ToString() + "|"))

It first turns 1|2|3 into |1|2|3| and then looks for |1| or |2| and will work no matter if the ID is first or last or somewhere in the middle.

But you really should restructure your database - it is usually a very bad idea to have information encoded like that and you already found out because you had to ask how to do something that should be trivial.

edited May 10, 2014 at 11:19

answered May 10, 2014 at 11:12

Daniel Brückner

59.8k16 gold badges103 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ammar Khan Over a year ago

I am getting this error now: Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.

Daniel Brückner Over a year ago

Add back the ToString() call - I thought you can leave it out but maybe not.

user3373870 · Accepted Answer · 2014-05-10 11:17:34Z

0

It's also worth noting that ToString() is supported in EF 6.1 http://blogs.msdn.com/b/adonet/archive/2014/03/17/ef6-1-0-rtm-available.aspx

answered May 10, 2014 at 11:17

user3373870

1,4962 gold badges13 silver badges18 bronze badges

Comments

serdar · Accepted Answer · 2014-05-10 11:30:10Z

0

context.Hotels
    .Where(h => h.HotelType.Split('|').Select(str=>Convert.ToInt32(str)).Contains(s.HotelTypeId))
    .Select(hotel => new Model.Hotel
       {
          HotelId = hotel.HotelID,
          HotelName = hotel.HotelName,
          HotelFileName = hotel.HotelFileName,
          StarRating = hotel.StarRating,
          CountryName = hotel.Country.CountryName,
          PlaceName = hotel.Place.PlaceName
       })

answered May 10, 2014 at 11:30

serdar

1,6271 gold badge21 silver badges31 bronze badges

Comments

Tolga Evcimen · Accepted Answer · 2014-05-10 11:33:31Z

0

Assuming your HotelType is int you can do this.

context.Hotels
    .Where(h => h.HotelType.Split("|").Select(ht=>int.Parse(ht))
    .Contains(s.HotelTypeId))
    .Select(hotel => new Model.Hotel
       {
          HotelId = hotel.HotelID,
          HotelName = hotel.HotelName,
          HotelFileName = hotel.HotelFileName,
          StarRating = hotel.StarRating,
          CountryName = hotel.Country.CountryName,
          PlaceName = hotel.Place.PlaceName
       });

answered May 10, 2014 at 11:33

Tolga Evcimen

7,39011 gold badges63 silver badges97 bronze badges

Collectives™ on Stack Overflow

Linq query where clause

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related