Correct way to get data

17 posts, 0 answers
  1. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 19 Sep 2011 Link to this post

    Hey there guys,

    I am new to OpenAccess ORM and LINQ, so please forgive my “101” type questions here. I am trying to figure out the correct way of retrieving data from multiple tables. Please give me a min to lay it all out.

    I have the following tables:

    UserGroups
    Settings
    SettingsGroups
    SettingParameters

    Each UserGroups row can have many Settings rows, and each Settings row is part of a single SettingsGroups row and can also have multiple SettingsParameters rows.

    I then have a method in my DAL to get the current users UserGroups settings. So I am trying to do something like this:

    using (BluefireDBContext dbContext = new BluefireDBContext())
            {
                //From SettingsGroup get Settings
                IList<Settings> settings = new List<Settings>();
                settings = dbContext.Settings.SelectMany(s => s.UserGroups.UserGroupID == usergroupid && s.SettingsGroups.GroupName == "Settings Group Name");

    But that does not work. I have tried so many things that now my method is a mess:

    public IList<SettingsDTO> getTrackingAPISettingsByUserGroupID(int usergroupid)
    {
        IList<SettingsDTO> groupapisettings = new List<SettingsDTO>();
     
        try
        {
            using (BluefireDBContext dbContext = new BluefireDBContext())
            {
                //From SettingsGroup get Settings
                IList<Settings> settings = new List<Settings>();
                settings = dbContext.Settings.SelectMany(s => s.UserGroups.UserGroupID == usergroupid && s.SettingsGroups.GroupName == "Settings Group Name");
     
     
     
                //IList<Settings> sets = new List<Settings>();
                //sets = dbContext.Settings.SelectMany(s => s.UserGroupID == usergroupid).AsQueryable<Settings>();
     
     
                //GET THE USERGROUPS API SETTINGS
                SettingsGroups setgroup = new SettingsGroups();
                setgroup = dbContext.SettingsGroups.Single(g => g.GroupName == " Settings Group Name ");
     
                string groupname = "";//settings..GroupName;
     
                ////GET AND LOOP THROUGH ALL FOUND SETTINGS
                //IList<Settings> settings = new List<Settings>();
                //settings = setgroup.Settings.Where(s => s.UserGroupID == usergroupid);
     
                foreach (Settings setting in settings)
                {
                    //LOOP THROUGH AND ADD ALL SETTINGS PARAMETERS TO DTO
                    IList<SettingParameterDTO> setparams = new List<SettingParameterDTO>();
     
                    foreach (SettingParameters parameter in setting.SettingParameters)
                    {
                        setparams.Add(new SettingParameterDTO(parameter.ParameterName, parameter.ParameterValue, Convert.ToBoolean(parameter.IsEnabled), Convert.ToBoolean(parameter.IsVisible)));
                    }
     
                    //ADD CURRENT SETTING TO COLLECTION
                    groupapisettings.Add(new SettingsDTO(groupname, setting.SettingName, setting.SettingValue, setparams, ""));
                }
            }
        }
        catch (Exception ex)
        {
            //TODO: ADD ERROR LOGGING AND HANDLING
        }
     
        return groupapisettings;
    }

    Can someone please help me on how to get all settings for a given UserGroupID and specific GroupName, in this case “Settings Group Name”… I want the Settings and each settings Parameters.

    I am executing SelectMany incorrectly obviously as I am getting this error, and frankly not even sure why i am doing that. It hates the fact that i am trying to put the results in a typed IList<>. I need some serious help...

    The type arguments for method 'System.Linq.Enumerable.SelectMany<TSource,TResult>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,int,System.Collections.Generic.IEnumerable<TResult>>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

    I am extremely frustrated and just need someone to show me the correct way to pull this data using OpenAccess and LINQ…
  2. Nikola
    Admin
    Nikola avatar
    27 posts

    Posted 21 Sep 2011 Link to this post

    Hello Duncan,

    You should be able to retrieve the information you need by using a simple Where query. Probably something like this:

    IQueryable<Setting> settings = dbContext.Settings.Where(s => s.UserGroups.UserGroupID == usergroupid && s.SettingsGroups.GroupName == "Settings Group Name");

    Please find attached example project which demonstrates the appropriate usage of .Select(..) and .SelectMany(..). Кeep in mind that the example is written solely for the purpose to clarify the usage of these methods and also that I created a model by your description therefore I ended up with something that is (apparently) different than yours. 

    I could also suggest you to have a look at our "Linq 101" example in our OpenAccess SDK.

    If you need further guidance please do not hesitate to ask. It would help immensely if you could attach an example .rlinq file which will give me something to work with.


    Kind regards,
    Nikola
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  3. DevCraft banner
  4. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 21 Sep 2011 Link to this post

    Thanks you so much, i will try this out and let you know...

    Duncan
  5. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 21 Sep 2011 Link to this post

    This works for me:

    IQueryable<Settings> settings
        = dbContext.Settings
        .Where(s => s.SettingName == "Some Value");
     
    foreach (Settings setting in settings)
    {
    ...

    But this does not:

    IQueryable<Settings> settings
        = dbContext.Settings
        .Where(s => s.UserGroups.UserGroupID == usergroupid && s.SettingsGroups.GroupName == "Some Value");
     
    foreach (Settings setting in settings)
    {
    ...

    Any idea why or what i am missing?

  6. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 22 Sep 2011 Link to this post

    Hi Duncan,
       Each table should have a unique indentifier (key) and I think from the names that you use that each table has an ID column.
    When quering it's going to be slower if you use strings rather than integer values.
    .Where(s => s.UserGroups.UserGroupID == usergroupid && s.SettingsGroups.GroupName == "Some Value");
    will be slower than
    .Where(s => s.UserGroups.UserGroupID == usergroupid && s.SettingsGroups.GroupID == settingsgroupid);

    If a Setting can only be in one SettingsGroup then the Setting should have a property for that,
    then your query could look like this
    .Where(s => s.UserGroups.UserGroupID == usergroupid && s.SettingsGroupID == settingsgroupid);

    When you say that the latter exsample does work for you. In what way does it not work.
    Do you get errors or is the result set empty.
    If it's not returning any records then verify that there are matching data in the tables. It has happened to me that my tests didn't match the test data.
  7. Nikola
    Admin
    Nikola avatar
    27 posts

    Posted 22 Sep 2011 Link to this post

    Hi Duncan,

    Both of your LINQ statements are valid and supported by Telerik OpenAccess ORM. Are you sure that there are any rows in the Settings table that satisfy the condition in the second statement?

    In case there are, could you please send me a sample project with the problem?


    Regards,
    Nikola
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  8. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 23 Sep 2011 Link to this post

    You guys are correct, this does work... There was a error in my data.

    Along the same lines and before i close this one up, if i want to insert or update the same tables how would i go about this?
  9. Nikola
    Admin
    Nikola avatar
    27 posts

    Posted 26 Sep 2011 Link to this post

    Hi Duncan,

    Executing CUD operations with OpenAccess ORM is rather easy and straightforward. For example you could go like this.

    using (SomeDBContext dbContext = new SomeDBContext() )
    {
        SomeEntity entity = new SomeEntity();
        dbContext.Add(entity);
        SomeEntity2 entity2 = new SomeEntity2();
        dbContext.Add(entity2);
         
        dbContext.SaveChanges();
    }

    In a nutshell, everything you adding to a context, deleting from a context, or modifying instances of entities that you have obtained by a context is persisted to the database when you call the SaveChanges() method.

    You could read our documentation in order to receive a better perception of how things are done.
    I would also recommend that you download and install our OpenAccess SDK which contains numerous examples showing OpenAccess features. Literally any example could show you how to do CUD with OpenAccess.

    Hope that helps and have a nice day.

    Greetings,
    Nikola
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  10. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 26 Sep 2011 Link to this post

    The only example i am not able to find is one dealing with multiple, relating entities... So inserts into multiple/joined tables in one operations...

    For example... Maybe i need to find the ID of a group the new user should be inserted in with. Do i select for this group and then add the user on this found entity? Does that make sense?

  11. Nikola
    Admin
    Nikola avatar
    27 posts

    Posted 28 Sep 2011 Link to this post

    Hi Duncan,

    I recommend you to check out the Sofia Car Rental examples in the OpenAccess SDK. They are complex examples which demonstrate many of the OA features. All of them could show you how to work with related entities. 

    Here is a short code snippet that could show you how to insert into related tables simultaneously.

    using (DBContext dbContext = new DBContext())
    {
        User user = new User();
        user.Username = "foo";
        user.Password = "bar";
     
        //Create a new UserGroup
        UserGroup userGroup = new UserGroup();
        userGroup.UserGroupCode = "foo bar";
     
        //set the new UserGroup to the navigation property of the new User
        user.UserGroup = userGroup;
     
        //Add just the user to the context. Adding the new user group is not neccessary
        dbContext.Add(user);
     
        //SaveChanges will persist both entities in the correct order
        dbContext.SaveChanges();
    }

    Do not hesitate to contact us if you need further clarification.

     
    Best wishes,
    Nikola
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  12. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 28 Sep 2011 Link to this post

    Hi Nikola,
    I have the OpenAccess SDK installed already, is the Sofia Car project in there? And can you point me to the file within that project that has these examples. It is all beginning to make sense now. Now i just need to put it all into practice. Thanks you for the examples.

    Duncan
  13. Nikola
    Admin
    Nikola avatar
    27 posts

    Posted 30 Sep 2011 Link to this post

    Hello Duncan,

    Please have a look at the Sofia Car Rental - WCF Plain Services example. In the SofiaCarRentalDAL project there are a couple of repository implementations, each working with a single entity. Check out the CarRepository class which is under the DTO folder. Its CreateInternal method is being used to create a Car and also contains business logic which will insert or update a RentalOrder by using the Car.RentalOrders navigation property.

    Hope that helps.

    Greetings,
    Nikola
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  14. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 30 Sep 2011 Link to this post


    I have been looking at those demos and there is one thing that I'm not sure about.

    How does one get the ID of a new object.
    Lets say that the user could create a new car and the unique ID of the car is generated by ORM (HighLow). Is there some way to add and be able to find the new car to available cars without have to clear the list of available cars and reread it from ORM.

    I'm not attempting to hijack this thread, I think this question is something that Duncan will ask sooner or later.
  15. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 30 Sep 2011 Link to this post

    is this the recomended way ?

    CarDto dtoObj = new CarDto();
    CarDto.Name = "New Car"; // and some more car properties
    using (CarRepository repository = new CarRepository())
    {
         dtoObj.CarId = repository.Create(dtoObj).CarId;
         repository.SaveChanges();
    }
    CarList.Add(dtoObj);

  16. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 03 Oct 2011 Link to this post

    I just realized that this will not work as the ID isn't assigned until the object is saved.

    Haven't seen a solution to this yet.
  17. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 04 Oct 2011 Link to this post

    Would this be the correct way ?

    CarDto dtoObj = new CarDto(); 
    CarDto.Name = "New Car"; // and some more car properties 
    using (CarRepository repository = new CarRepository()) 
         var oaObj  = repository.Create(dtoObj); 
         repository.SaveChanges(); 
         dtoObj.CarId = oaObj.CarId;
    CarList.Add(dtoObj);
  18. Nikola
    Admin
    Nikola avatar
    27 posts

    Posted 05 Oct 2011 Link to this post

    Hi Bjössi,

    In general the identity of a table is calculated during the SaveChanges() method call and the identity property of the entity which is passed through the Add() method is updated correspondingly.
    This means that you can use the same object instance you have passed to the Add() method as if you have executed a query for it and you don't need to execute an additional query.

      When working with DTO objects its not so much of a different story. You only need to figure out a way to pass the ID from the server to the client. A standard approach would be to populate the DTO and return it through the service call which gives you certainty over the fact that if the business logic on the SQL Server(if there is such) changes other columns than the primary key you would be aware of that as well. 
    Another approach would be to send just the ID over the wire which would minimize traffic. 

    With the information above in mind, the example you have given would only work if the
    repository.Create(dtoObj);
    method calls OpenAccessContext.SaveChanges() internally (thus making the subsequent repository.SaveChanges() irrelevant) and then converts the given entity to a DTO and returns it. The rest of your example code should work fine.
    Hope that helps.
     
    Best wishes,
    Nikola
    the Telerik team

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

Back to Top
DevCraft banner