InnerSelect - c1DataSource

InnerSelect - c1DataSource

Posted by: james on 17 August 2020, 1:53 pm EST

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 17 August 2020, 1:53 pm EST

  We are using the Entity Framework, a c1DataSource with a ViewSource in Managed Virtual Mode, and a c1FlexGrid.
  
  We have a textbox that we use to filter the ClientView from the ViewSource.
  
  We use the .AsFiltered() method to filter the ClientView.
  
  This all works well.
  
  Virtual Mode works, and the filtering works.
  
  Basically, we are filtering a database table named Customers.

  I would like to know how to tackle the following task, with using the above configuration.

  We would like to perform an inner select, but we want to pass through a variable from code.
  
  For example,

  (SELECT TOP 1 ClientContact.Description FROM ClientContact WHERE ClientContact.ContactID = @ContactID) AS Relationship
  

  Where @ContactID is a variable we want to programmatically pass through.
  
  For example, if I was using SQL and C# I would do something along the lines of

  int contactId = 3;
  string sqlQuery = “SELECT CustomerId, CustomerName, (SELECT TOP 1 ClientContact.Description FROM ClientContact WHERE ClientContact.ContactID = “ + contactId.ToString() + “) AS Relationship FROM Customers”;
  

  Is this possible to achieve with the ClientView etc… as detailed above?

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 17 August 2020, 8:40 pm EST

  Hi,

  You can use the following code to achieve this:

  int contactId = 3;
  var relationship = _scope.GetItems<ClientContact>().AsFiltered(c => c.ContactID == contactId).Select(c => c.Description).First();
  var view = _scope.GetItems<Customer>().Select(c => new
  {
      c.CustomerId,
      c.CustomerName,
      Relationship = relationship
  }).AsDynamic();
  
  c1FlexGrid1.DataSource = view;
  

  For more information, check the C1DataSource documentation page describing this.
  
  https://www.grapecity.com/componentone/docs/win/online-datasource/WorkingwithDataSourcesinCode2.html

  Regards,
  
  Jitender

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 18 August 2020, 12:32 pm EST

  thank you. I apologise, I actually wrote the inner select incorrect in my original request. The inner select not only needs a variable in it, but also link to the original table like below:
  
  eg. I need something like

  
  int contactId = 3;
  string sqlQuery = “SELECT CustomerId, CustomerName, (SELECT TOP 1 ClientContact.Description FROM ClientContact WHERE ClientContact.ClientID = Customers.CustomerId AND ClientContact.ContactID = “ + contactId.ToString() + “) AS Relationship FROM Customers”;
  

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 18 August 2020, 3:52 pm EST

  You should have a navigation property for ClientContact on Customer (depending on your schema, it might be available through another navigation property, see your edmx file for how you can get to it).
  
  Here is one example showing how you may be able to do it:

  var view = _scope.GetItems<Customer>().
      .AsFiltered(c => c.ContactID == contactId.ToString())
      .Select(c => new
      {
          c.CustomerId,
          c.CustomerName,
          Relationship = c.ClientContact.Description
      }).AsDynamic();
  

  Also, note that this usage is independent of C1 controls. You can refer to the EF documentation to see how to use navigation properties:
  
  https://docs.microsoft.com/en-us/ef/ef6/fundamentals/relationships

  Regards,
  
  Jitender

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 19 August 2020, 1:58 pm EST

  Hi Jitender

  Thank you. I got it working with Navigation Property, but it does not seem to be using the virtual mode any more and doing a full load of the database table. I have tried to place the Navigation property in the ClientView, but I am getting an error that anonymous type cannot be convert to the ClientView.

  Is there a way to use a Navigation property as part of a select on the ClientView to take advantage of Virtual Mode?

  Previous Code:

  ClientView<FusionData.Entities.Customer> customerView_runningView =
  ((ClientView<FusionData.Entities.Customer>)c1DataSource1.ViewSources["Customers"].BaseView;
  
  customerView_runningView = 
  ((ClientView<FusionData.Entities.Customer>)customerView_runningView).
   AsFiltered(c => c.Companyname.ToLower().Contains(word) || 
  c.Accountcode.Equals(word) || c.CommonName.ToLower().Contains(word));
  

  Attempted Code:

  string clientId = "930";
  ClientView<FusionData.Entities.Customer> customerView_runningView =
  ((ClientView<FusionData.Entities.Customer>)c1DataSource1.ViewSources["Customers"].BaseView;
  
  customerView_runningView = 
  ((ClientView<FusionData.Entities.Customer>)customerView_runningView).
   AsFiltered(c => c.Companyname.ToLower().Contains(word) || 
  c.Accountcode.Equals(word) || c.CommonName.ToLower().Contains(word)).
     Select(c => new
     {
      c.Accountcode,
      c.Companyname,
      Relationship = c.ClientContacts_ContactId.Where(o => o.ClientId == clientId).First().ContactType.ContactType_
     });
  
  

  Thanks

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 20 August 2020, 12:17 am EST

  Hi,

  Data for navigation properties is only loaded when required so it should automatically support virtual mode.

  I have tried to place the Navigation property in the ClientView, but I am getting an error that anonymous type cannot be convert to the ClientView.
  
  customerView_runningView =
  
  ((ClientView<FusionData.Entities.Customer>)customerView_runningView).
  
  AsFiltered(c => c.Companyname.ToLower().Contains(word) ||
  
  c.Accountcode.Equals(word) || c.CommonName.ToLower().Contains(word)).
  
  Select(c => new
  
  {
  
  c.Accountcode,
  
  c.Companyname,
  
  Relationship = c.ClientContacts_ContactId.Where(o => o.ClientId == clientId).First().ContactType.ContactType_
  
  });

  Select is a projection operation which creates an anonymous type (depending on the properties specified in Select’s Selector lambda), it cannot be converted to ‘Customer’. You can still bind the result to the grid since it returns View<'a> which can be used as a data source.

  Regards,
  
  Jitender

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 20 August 2020, 1:16 pm EST

  Hi Jitender

  I was able to make a view and bind it to the grid, but it was not using the virtual mode of the c1datasource as far as i could tell… that’s why I tried to do the command as part of the client view.
  
  I will redo and add some debug features in to inspect the Virtual Mode, but from my initial tests it was doing a complete load of the table the first time, and then used it from the cache… whereas before when virtual mode was working, the initial load time was a fraction of a second.
  
  Thank you for your help, and I’ll do some debugging and get back to you.

  Thanks

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 20 August 2020, 2:30 pm EST

  Hi Jitender

  When my C1FlexGrid is bound to the C1DataSource object, with a DataMember of the name of the ViewSource, the Visual Studio Output window shows me debug messages: VirtualView: requesting range: 50 - 149… This indicates that VirtualMode is working.

  If I bind the C1FlexGrid to a dynamic view created from c1DataSource1.ClientScope.GetItems()… then these messages do not occur, and also the time taken for initial display is increased dramatically… these two indicators make me believe that VirtualMode only works when the grid is bound to the datasource directly as per above paragraph.

  Can you please confirm that VirtualMode is supported when binding a dynamic view to the C1FlexGrid as described above?

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 23 August 2020, 8:22 pm EST

  Hi

  Sorry, you are right, virtual mode is not supported in dynamics views.
  
  You will need to modify the FilterDescriptors property of the required ViewSource, and then bind to C1DataSource and specify that ViewSource as the DataMember.

  For example:

  var personSource = c1DataSource1.ViewSources["People"];
  personSource.FilterDescriptors.Add(new C1.Data.DataSource.FilterDescriptor("PersonType", C1.Data.DataSource.FilterOperator.IsEqualTo, "SC"));
  c1FlexGrid1.DataSource = c1DataSource1;
  c1FlexGrid1.DataMember = "People";
  

  As for the nested query, let me confirm with the developers if there is a way to do it.

  Regards,
  
  Jitender

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 25 August 2020, 11:21 am EST

  Hi Jitender

  FilterDescriptors are what we currently use - I figured that out when I first set it up, that it was the way for Virtual Mode to work.
  
  Please check with the developers if there is a way to do the nested query.

  Thanks

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 25 August 2020, 9:08 pm EST

  Hi,

  I’ll update this thread once we get a response from the concerned team [Internal Tracking ID: 458604].

  Regards,
  
  Jitender

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 30 August 2020, 11:35 am EST

  Hi Jitender

  Thank you. I look forward to the response.

  Thanks

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 31 January 2021, 12:58 pm EST

  Hi Jitender

  Just checking if there has been any update to this?

  Thanks
  
  James

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 31 January 2021, 7:45 pm EST

  Hi James,

  We are getting in touch with the developers for an update and will get back to you soon.

  Regards,
  
  Prabhat Sharma.

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 11 February 2021, 1:15 pm EST

  Hi Prabhat

  Thank you.

  Kind Regards
  
  James

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 12 April 2021, 6:40 pm EST

  Hi Prabhat

  Did you get an update from the developers regarding the change? Thanks

  Kind Regards
  
  James

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 15 April 2021, 9:00 pm EST

  Hi James,

  Our developers are working on this issue and will share the updates as soon as we receive anything from the devs end.

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 27 April 2021, 8:17 pm EST

  Hello James,

  The developers need a stripped-down sample of what you are doing to further investigate this issue. Please provide the needful.

  Regards,
  
  Prabhat Sharma.

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 10 May 2021, 3:36 pm EST

  Hi Prabhat

  Thanks. I am currently on holidays, and will respond with the required information when I return next week.

  James

• 

  james

  • Post Options:
  • Link
    Copy

  Posted 16 May 2021, 6:32 pm EST

  Hi Prabhat

  By a stripped down sample, do you mean a visual studio project that builds and runs? Or do you mean just snippets of code?

  Above when discussing the issue with Jitender, he mentioned that Dynamic Views do not support Virtual Mode, and that FilterDescriptors must be used instead.

  This means my problem will be solved by either
  
  1 - Dynamic Views supporting Virtual Mode
  
  or
  
  2 - or c1DataSource / filter descriptors allowing a nested filter…

  let me know what you would like, and i will create a sample.

  thank you.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 16 May 2021, 7:44 pm EST

  Hello James,

  We are asking the developers what exact information they need to investigate the issue and will let you know soon.

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 16 May 2021, 7:53 pm EST

  Hello,

  The developers need a VS project sample using the filter description.

  Regards,
  
  Prabhat Sharma.