-1

in my database i save users tag in table the table structure is Table Name : User

Id Name   Tags
1  Jack   White,Yellow,Green
2  smith  Yellow,Green
3  smith  Blank

....

the condition is:

string[] tags = {"Yellow","Green"};

I hope can get the data from database,so this is my lambda:

 unitOfWork.Repository<User>().find(x=>!x.Any(x.Tag.......);

the sql like:

Select * from Users Where Tags not in('Yellow','Green')

but the tags in database is long string with ","

denli8
  • 89
  • 2
  • 7
  • 5
    The best option is to normalize your schema so that `Tags` becomes a table that is linked to `Users` with a `optional one to many` relationship. Is that possible? – Igor Aug 04 '16 at 10:41
  • In [this question](http://stackoverflow.com/a/33658220/5089204) I placed an example how one could create an `IN` clause form a delimited string dynamically. But Igor is right: Your data is breaking 1NF. This is something you should never do... – Shnugo Aug 04 '16 at 10:44
  • Which version of SQL-Server? Starting with 2016 you might use the new built-in Split method... – Shnugo Aug 04 '16 at 10:48
  • The best solution is to fix the schema and create a separate table to match IDs to tags. You could use a free text search query to search for specific words, but that's a bit of overkill. You could also *cache* non-sensitive user data (eg ID and tags only? maybe name?) and generate a dictionary keyed by Tag to allow quick searches on the cached items – Panagiotis Kanavos Aug 04 '16 at 11:19

1 Answers1

-1

If you have big data, this is not a good solution But you can after you select all data.

var result = db.Users.ToList()
               .Where(i => i.Tags.Split(',').Any(t => tags.Contains(t))).ToList();

I think, normalize your schema looks like better.

Other option:

You can use SqlQuery

class Users
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Tags { get; set; }
}


string[] tags = {"Yellow","Green"};

var whereClause = String.Join(" OR ", tags.Select(i => String.Format("Tags LIKE %{0}%", i));

var query = String.Format(@"
                            SELECT 
                                * 
                            FROM 
                                Users 
                            Where 
                                {0}
                           ", whereClause);

var result = dbContext.Database.SqlQuery<Users>(query);

Query looks like:

SELECT 
    * 
FROM 
    Users 
Where 
    Tags LIKE %Yellow% OR Tags LIKE %Green%
neer
  • 4,031
  • 6
  • 20
  • 34
  • This is a bad solution even if you have very little data. You are loading *everything* in memory just to do a split on a field. At least, load *only* the tag and ID, not the entire user object. Once you find the matching IDs, you can do another query to load the relevant objects – Panagiotis Kanavos Aug 04 '16 at 11:04
  • @PanagiotisKanavos I dont think so. `This is a bad solution even if you have very little data` – neer Aug 04 '16 at 11:06
  • You are loading everything in memory (including any blobs like pictures etc), then scan every row without any benefit of indexing generating temporary string objects, just to find a subset of users. This will be a constant waste of RAM, CPU (due to scanning, splitting *and* garbage collection) and bandwidth, repeated for each individual request. This can work only by loading only what's necessary, caching the data to avoid needless querying, and generate a dictionary upon loading to avoid searching all users. – Panagiotis Kanavos Aug 04 '16 at 11:17
  • That is why I said that `If you have big data, this is not good solution` If you have little data (`including any blobs like pictures etc`) The impact will not be much. – neer Aug 04 '16 at 11:21
  • For a web application it's not the size of the data that matters so much as the frequency of requests. A bad approach will kill a site if it has even moderate traffic. In combination this approach would fail even for small sites (which typically run on *small* servers). It's *not* hard to implement a good solution for this problem – Panagiotis Kanavos Aug 04 '16 at 11:23
  • Sometimes all developers are forced to resort to this method. Performance or Load testing will reveal if there are problems in practice. – neer Aug 04 '16 at 11:31