Filter column IQueryable C#

Question

I have a queryable which is returning a table with 4 columns:

ItemName | Country A | Country B | Country C |

I have some authorization processes in my app and some users should see only specific country columns.

For example, user with claim country=A should only see:

ItemName | Country A |

Is there a way to filter the columns based on the name of the column?

IQueryable<AllCountries> ResultsVM = _context.AllCountries;
            var country = IdentityExtensions.GetCountryId(User);             

            if (country != null)
            {
                Type elementType = ResultsVM.ElementType;
                foreach (PropertyInfo pi in elementType.GetProperties())
                {

                        if (country==pi.Name)
                        {
                            ResultsVM= _context.AllCountries
                                            .Select(t => new AllCountries
                                   {                                          
                                       ItemName=t.ItemName,
                                       ?? only the column which matches the country
                                   });
                        }

                }

            }

So far, I know when a column of my IQueryable corresponds to the country of the user but I don't know in my Select how to show it and remove the others...

Update

My ViewModel ResultsVM which stores the IQueryable and will be used in my View in my MVC web project, is defined as a list of:

public class AllCountries
    {       
        [DisplayName("Item")]
        public string ItemName { get; set; }
        public int? Country1 { get; set; }
        public int? Country2 { get; set; }
        public int? Country3 { get; set; }        
    }

Thank you

Sylvain

score 0 · Answer 1 · answered May 17 '17 at 04:13

Considering the name prefix of Columns is Country and the condition on which you want to select that column is suffix of that column for Ex:

Country  + '1'
Country  + '2'
Country  + '3'
Country  + '4'

Then you can create a dynamic select in your LINQ Query like this:

public  class Country
        {
            public string Condition { get; set; }
            public string  Country1 { get; set; }
            public string Country2 { get; set; }
            public string Country3 { get; set; }
        }  

public void GetData()
        {
            List<Country> liCountry = new List<Country>();
            liCountry.Add(new Country { Condition = "1", Country1 = "2", Country2 = "3", Country3 = "4" });
            liCountry.Add(new Country { Condition = "2", Country1 = "20", Country2 = "30", Country3 = "40" });
            liCountry.Add(new Country { Condition = "3", Country1 = "21", Country2 = "31", Country3 = "41" });
            string temp = "2";
            var liResult = liCountry.Where(w => w.Condition == temp).Select(s => new {result = s.GetType().GetProperty("Country" + temp).GetValue(s, null) }).ToList();

        }

In the select statement I have used Reflection to get the column name dynamically.

Thank you. How do you do in your select to return a result which contains the correct column name (eg.: country2 in your example) instead of the generic 'result' column name? It is important to me in order to make the mapping in my view. — Sylvain C., May 17 '17 at 06:08
@sylvain77 didn't get what you are asking, Could you please elaborate? — Amit Bisht, May 17 '17 at 07:02
Sure. I have updated my question. I need my IList resultsVM to be based on the class defined in my question. In your example, it would be to have liResult with "country2" as being the title of my column in my list and not "result" as it is shown in your example. I need to keep the naming consistent in order to use it after in my view. — Sylvain C., May 18 '17 at 06:02
Ok found the solution to get dynamic properties as well: http://stackoverflow.com/questions/16516971/linq-dynamic-select — Sylvain C., May 19 '17 at 04:10

Filter column IQueryable C#

1 Answers1