CODEWITHSUNDEEP
c#linq
bita pinches
bita pinches

Reputation: 63

Convert SQL query to Linq when querying from a list

I have this SQL query I would like to convert it to linq query, the query works fine with in the database.

The problem is I have cached the response and will create a list. I want to query from the cached list not the database with linq. I am able to get the object but unable to get a specific column which is passed as a string eg. 'newid' by client depending on the action being performed, i.e. creating, updating etc

How can I get the same result with linq or another way?

public class tbluserfunctions
{
    public int id { get; set; }
    public int userroleid { get; set; }
    public int componentid { get; set; }
    public int newid { get; set; }
    public int editid { get; set; }
    public int viewid { get; set; }
    public int deleteid { get; set; }
}

// SQL query
SELECT @column 
FROM tbluserfunctions 
WHERE userroleid = @roleid 
  AND componentid = @cid;

Here is the method:

public int UserHasAccess(string col, int cid, int roleid)
{
    var count = (from d in data()
                 where d.componentid == cid && d.userroleid == roleid
                 select d);
    // I get the object(tbluserfunctions) but from the method am 
    // passing in the column that its value is what I need to return
    // Some code here which I cannot figure out so I can return the value
    return value;
}

Upvotes: 0

Views: 111

Answers (2)

Ben
Ben

Reputation: 787

You can use reflection like below. It will get the type of the object, select the property you want, then pass in your instance so that is has something to retrieve from .

   //here is the method
      public int UserHasAccess(string col, int cid, int roleid)
        {
        var count = (from d in data()
                     where d.componentid == cid && d.userroleid == roleid
                     select d);
        tbluserfunctions.GetType().GetProperty(col).GetValue(tbluserfunctions);
        return value;
       }

Upvotes: 2

Hamid Jolany
Hamid Jolany

Reputation: 880

in this case you can parametrize your input and then select columns as a new class, like bellow

DataContext1 db = new Datacontext1;
var roleid = 123; /* for example */
var result = db.tbluserfunctions.Where(x=>x.userroleid == roleid)
             .Select(s=> new tbluserfunctions()/*this is your class*/{
                         id = s.id,
                         userroleid = s.userroleid,
                         componentid = s.componentid,
                         newid = s.newid,
                         editid = s.editid,
                         viewid = s.viewid,
                         deleteid = s.deleteid
}).ToList();

you have a list of objects now, then you can choose each column you want.

Upvotes: 0

Related Questions