none
How to get a collection of entities from a table that has no primary key with different fields? RRS feed

  • Question

  • I am understanding the Entity Framework much better now.  I can use the Find and First methods.  I can also make a linq query and hardcode the field passing in a variable like this:

    State = "CT";
    var query1 = from Sales_Regions srs in DM.Sales_Regions
        where srs.Territory.Equals(State)
        select srs;

    But what if the user has many choices?  What if I may want to filter on one (or more than one if possible) out of a couple of dozen fields?

    Say I have fields like SalesRep, Month and State.  I would like to do something like this:

    var query1 = "from Sales_Regions srs in DM.Sales_Regions " +
        "where srs." + Field + ".Equals(" + Value + ") " +
        "select srs";

    to result in this:

    var query1 = from Sales_Regions srs in DM.Sales_Regions 
        where srs.State.Equals("CT") 
        select srs;
    
    OR
    
    var query1 = from Sales_Regions srs in DM.Sales_Regions 
        where srs.Month.Equals(8) 
        select srs;
    
    OR
    
    var query1 = from Sales_Regions srs in DM.Sales_Regions 
        where srs.SalesRep.Equals(217) 
        select srs;
    
    OR numerous other options. . .

    Can this be done with the Entity Framework?  Or do I need to write a data manager to work alongside the Entity Framework?


    • Edited by MarDude Friday, August 26, 2016 8:45 PM Typo
    • Moved by DotNet Wang Monday, August 29, 2016 6:08 AM EF related
    Friday, August 26, 2016 8:45 PM

Answers

  • Hi MarDude,

    >>I found Dynamic linq.

    According to your description, I reproduce your issue on my side by using your code snippet, it seems that Dynamic linq doesn't support Field Name as parameter. please modify your code like this:

    string fieldName = txtField.Text

    var query = SC.Sales_Regions.Where(fieldName + "= @0", txtValue.Text); srsl = query.ToList(); dataGridView1.DataSource = srsl;

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 29, 2016 7:00 AM
    Moderator

All replies

  • You could create a generic data access layer that accepts dynamic where conditions, e.g.:

    public virtual IList<T> GetList(Func<T, bool> where)
            {
                List<T> list;
                using (var context = new Entities())
                {
                    IQueryable<T> dbQuery = context.Set<T>();
                    ...
                    list = dbQuery
                        .Where(where)
                        .ToList<T>();
                }
                return list;
            }
    
    ...
    
    public IList<Employee> GetEmployeesByDepartmentName(string departmentName)
            {
                return _employeeRepository.GetList(e => e.Department.Name.Equals(departmentName));
            }
    

    Please refer to my blog post about how to implement a generic data access layer using EF for more information about this and a full example: https://blog.magnusmontin.net/2013/05/30/generic-dal-using-entity-framework/


    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Friday, August 26, 2016 9:29 PM
  • I found Dynamic linq.  I think I am close.  But I do not get anything back.

                        var query =
                            SC.Sales_Regions.Where("@0 == @1", txtField.Text, txtValue.Text);
                        srsl = query.ToList();
                        dataGridView1.DataSource = srsl;

    Have a good weekend!!

    Friday, August 26, 2016 9:30 PM
  • Hi MarDude,

    >>I found Dynamic linq.

    According to your description, I reproduce your issue on my side by using your code snippet, it seems that Dynamic linq doesn't support Field Name as parameter. please modify your code like this:

    string fieldName = txtField.Text

    var query = SC.Sales_Regions.Where(fieldName + "= @0", txtValue.Text); srsl = query.ToList(); dataGridView1.DataSource = srsl;

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 29, 2016 7:00 AM
    Moderator
  • That did the trick!!  Thanks Cole!
    Monday, August 29, 2016 1:21 PM