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…
16 Answers, 1 is accepted
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 >>
Duncan
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?
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.
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 >>
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?
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 >>
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?
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 >>
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
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 >>
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.
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);
Haven't seen a solution to this yet.
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);
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);
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 >>