InnerSelect - c1DataSource

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

  • 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?
  • Replied 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
  • Replied 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”;
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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<Customer>().... 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?



  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 30 August 2020, 11:35 am EST

    Hi Jitender

    Thank you. I look forward to the response.

    Thanks
  • Replied 31 January 2021, 12:58 pm EST

    Hi Jitender

    Just checking if there has been any update to this?

    Thanks
    James
  • Replied 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.
  • Replied 11 February 2021, 1:15 pm EST

    Hi Prabhat

    Thank you.

    Kind Regards
    James
  • Replied 12 April 2021, 6:40 pm EST

    Hi Prabhat

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

    Kind Regards
    James
  • Replied 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.
  • Replied 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.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels