CODEWITHSUNDEEP
entity-framework-4
guest82
guest82

Reputation: 123

Query to filter comma separated string table entry with Entity Framework?

I have a table with the following columns:

equipment_id | equipment_name | department_id

department_id is stored with comma separated string of ids (i.e. 1,4,5,7)

Given a department_id I'd like to get all equipment items that are associated with that dept id.

Just wondering if there's an elegant way to write the query or do I have to grab each equipment item, split the department_id column, check if the id matches, and add the equipment item back to a separate list?

Upvotes: 0

Views: 829

Answers (3)

Rick Matthews
Rick Matthews

Reputation: 26

Here is a solution that will return all records that contain a particular department_id. 

var start = department_id + ",";
var end = "," + department_id;
var contains = "," + department_id + ",";

var results = from e in context.Equipment
              where (e.department_id.StartsWith(start)
              || e.department_id.EndsWith(end)
              || e.department_id.Contains(contains)
              || e.department_id.Equals(department_id))
              select e;

Upvotes: 0

Adam Tuliper
Adam Tuliper

Reputation: 30152

You have two main options here

  1. Split with a UDF that returns a list of integers. You map this UDF in EF and call it directly.

  2. Split in code after its returned. If you use a repository layer for that item the impact on the callers is minimal as all they do is request it from your repository which handles the splitting.

Upvotes: 0

Erik Philips
Erik Philips

Reputation: 54638

Is there anyway to get the data normalized properly? A single column should never contain a comma seperated string of ids.

Otherwise, most answers to your specific question will perform poorly compared to a properly normalized database.

Upvotes: 1

Related Questions