CODEWITHSUNDEEP
c#.netentity-frameworklinqkendo-asp.net-mvc
Shashi
Shashi

Reputation: 1182

C# Linq to Entity Framework multiple unrelated queries

I have a situation where I need data from multiple database tables.

Table 1 - has list of columns which needs to be displayed on front end html, angular kendo grid - which is configurable from separate Admin configuration.

Table 2 (joining of some other tables)- has the data which needs to be displayed on the angular front end.

My linq here which I am using currently is as below.

Query 1: to get list of columns to be displayed on Grid

var columns = from cols in _context.columns
              select cols.colNames;

Query 2: Get the actual data for list 

var data = from cust in _context.customer
           join details in _context.custDetails on cust.id equals details.custid
           join o in _context.orders on cust.id equals o.custid
           where cust.id == XXXX
           select new Customer
           {
            Id = cust.Id,
            Name = cust.Name,
            Address = details.Address,
            City = details.City,
            State = details.State,
            OrderDate = o.OrderDate,
            Amount = o.Amount
           //15 other properties similarly
          };

returns IQueryable type to Kendo DataSourceRequest

Currently, From my ui I have been make two api calls one for columns and one for getting the actual data, and show/hide the columns which are configured in the columns table.

But the problem is if anyone looks at the api calls on the network or on browser tools they could see the data being returned for the columns that are to be hidden which is a security problem.

I am looking for a single query for my api which returns the data using second query which should be smart enough to send the data only for configured columns (there could be 30 different columns) and set the other properties to null or doesn't select them at all. there are some properties which needs to be returned always as they are being used for some other purpose.

I searched many resources on how could I generate dynamic linq select using the configured columns.

Please some one help me in resolving this problem

Upvotes: 0

Views: 961

Answers (1)

Viru
Viru

Reputation: 2246

you can do something like this. Assuming you columns tables a Boolean column Display and when it is true Column will be displayed and when it is false it wont be displayed.

 var columns = (from cols in _context.columns
                  select cols).ToList(); // note I am getting everything not just column names here...


   var data = from cust in _context.customer
           join details in _context.custDetails on cust.id equals details.custid
           join o in _context.orders on cust.id equals o.custid
           where cust.id == XXXX
           select new Customer
           {
            Id = cust.Id,
            Name = cust.Name,
            Address = details.Address,
            City = details.City,
            State = details.State,
            OrderDate = o.OrderDate,
            Amount = o.Amount
           //15 other properties similarly
          }.ToList();

var fileterdData = from d in data
                   select new Customer
                  {
                     Id = DisplayColumn("ID",columns)? cust.Id:null,
                     Name =  DisplayColumn("Name",columns)? cust.Name:null,
                     Address =  DisplayColumn("Address",columns)? details.Address:null,
                      // do similarly for all other columns
                  }.AsQueryable(); // returns IQueryable<Customer>

private bool DisplayColumnn(string columnName,List<Columns> cols)
{
return cols.Where(x=>x.ColumnName==columnName).First().Display();
}

So now you will have this code as part of one web API call which is going to do two SQL calls one to get columns and other to get data then you will use Linq To Entity filter columns which you dont want ( or want them to null). return this filtered data back to UI.

Upvotes: 1

Related Questions