LINQ Query from Object Array Inside Database column string

Question

Sorry, it was difficult to choose a good title for the doubt.

I'm querying my Repository using LINQ on a specific column string called "Parameter" that have ';' separator. The database column it's a pattern like "name1=value1;name2=value2;name3=value3" and I'm able to convert it to an Object called GenericParameter[] spliting the string by ';'.

My GenericParameter class that deal with Parameter looks like this:

public class GenericParameter
    {
      public string Name {get; set;} //name1
      public string Value {get; set;}//value1

public static GenericParameter[] GetParameters(string parameters)
        {
            List<GenericParameter> chargingParameterList = new List<GenericParameter>();

            if (!string.IsNullOrEmpty(parameters))
            {
                string[] splitedChargingParameter = parameters.Trim().Split(';');

                foreach (string parameter in splitedChargingParameter)
                {
                    string[] pair = parameter.Split('=');
                    if (pair.Length != 2) continue;

                    GenericParameter genericParameter = new GenericParameter()
                    {
                        Name = pair[0].Trim(),
                        Value = pair[1].Trim()
                    };

                    chargingParameterList.Add(genericParameter);
                }
            }
            return chargingParameterList.ToArray();
        }
    }

The method I'm working on looks like this now:

public ICollection<MyData> GetMyDataByParameter(string param)
{
//param="name1=value1;name2=value2"

List<MyData> dataToReturn = context
                            .MyDataRepository
                            .AsQueryable()
                            .Where(p => p.ParameterStr.Contains(param))
                            .ToList();
return dataToReturn;
}

I'm refactoring it, because in this way my "param" can be passed in different orders like "name2=value2;name1=value1" and may not match the database column Parameter all the time.

I'm trying to do something like this:

public ICollection<MyData> GetMyDataByParameter(string param)
    {
    GenericParameter[] receivedParamArray = GenericParameter.GetParameters(param);
    //param="name1=value1;name2=value2"
    //GenericParameter[0].Name is "name1"
    //GenericParameter[0].Value is "value1"

    // TODO: QUERY the records on repository that matchs all the
    //receivedParamArray
    List<MyData> dataToReturn = context
                                .AsQueryable()
                                //.Where(p => p.ParameterStr.Contains(receivedParamArray))
                                .ToList();

    return dataToReturn;
    }

How can I query the repository column Parameter (string) that matchs all receivedParamArray. The query represented by the commented Where will not work.

Please, I appreciate any suggestions.

First, get rid of your GenericParameter class and use Dictionary<string,string>. No need to reinvent the wheel again. — Robert McKee
– Robert McKee, Commented Nov 3, 2015 at 21:40

Robert McKee · Accepted Answer · 2015-11-03 22:04:21Z

1

public static IQueryable<MyData> FilterByParameters(this IQueryable<MyData>,Dictionary<string,string> dict)
{
  IQueryable<Mydata> query=this;
  foreach(var entry in dict)
  {
     string val = String.Format(";{0}={1};",entry.Key,entry.Value);
     query=query.Where(a=>(";"+a.ParameterStr+";").Contains(val));
  }
  return query;
}
public static IQueryable<MyData> FilterByParameters(this IQueryable<MyData>,string s)
{
  return this.FilterByParameters(GetParameters(s));
}
public static Dictionary<string,string> GetParameters(string s)
{
  return s.Split(';')
    .Where(t=>t.Contains("="))
    .ToDictionary(t=>t.Split('=')[0].Trim,t=>t.Split('=')[1].Trim());
}

Used like this:

var results=db.MyData.FilterByParameters("name1=value1");

or

var search=new Dictionary<string,string>{{"name1","value1"},{"name2","value2"}}; 
var results=db.MyData.FilterByParameters(search);

edited Nov 3, 2015 at 22:04

answered Nov 3, 2015 at 21:46

Robert McKee

21.5k1 gold badge48 silver badges64 bronze badges

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

3 Comments

Robert McKee Over a year ago

Note that this will not be fast. It interally will use a LIKE %..% to find the parameters, which won't be able to use an index, so it will cause a complete table scan, but there isn't really a way around that with your current table design.

Robert McKee Over a year ago

I used "MyData" as the type. Just replace with whatever type your repository returns. You can also make this generic, and use a lambda to request the field that will contain the parameters so you can use the same logic over multiple tables if you need.

renanroncarati Over a year ago

Thank you @Robert Mckee . Inspired in your answer I found my solution. I didn't use Dictionary but I used the IQueryable logic and the foreach iterating on a splited list based on "name1=value1;name2=value2;name3=value3" and then I query the Repository. string[] parameters = referer.Split(';'); foreach (var param in parameters) { query = query.Where(p => p.ParameterStr.ToLower().Contains(param.ToLower())); }

Collectives™ on Stack Overflow

LINQ Query from Object Array Inside Database column string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related