 |
|
| .NET DotNet Forum Index » ADO .NET Forum » EF: selecting all the relations of a relation... |
|
Page 1 of 1 |
|
| Author |
Message |
| Markla... |
 Posted: Fri Jun 12, 2009 3:59 am |
|
|
|
Guest
|
Hi, I have been using EF since it's release, for the most part, it's saved me
thousands of lines of code. (This is a repost at the request of MS)
I've a scenario I havn't been able to solve with well performing code.
Imagine a model of a phone book, with Companies, Departments and People. A
company has an ID and name, department has an ID and name, and a person has
an ID and name (keeping it simple).
How can I write an entities statement which will return a collections of all
people under a company (regardless of department)?
I considered using something like this:
context.People.Where(p=>p.Department.Company.CompanyId == CompanyIdSearched)
However, my understanding is this goes back to the database to pull the data?
I need to query based on the in-memory data, so I can include data which has
changed but not yet been saved (eg. to check if I've already added a Person
in an import).
What's the most efficient way to query the in-memory context, to get all
People who work for a Company?
Thanks in advance 
+M |
|
|
| Back to top |
|
|
|
| sqlguru... |
| Posted: Fri Jun 12, 2009 7:26 am |
|
|
|
Guest
|
The entity framework, linq, dlinq, datasets in the .NET framework are
all "hacks" made for idiots that have no idea about architecture,
design, databases, application requirements process. Instead, the
idiots write their applications using these "hacks" so that after a
couple of months, it displays "Application not Responding" and "Server
timeout" messages.
Get a beginners level book on application architecture, database
design, and application design. A hybrid developer (one that knows
about writing applications and SQL) is much better than an idiot that
uses ORMs and "RAD" tools.
On Jun 12, 5:59 am, Markla <mar... at (no spam) newsgroup.nospam> wrote:
Quote: Hi, I have been using EF since it's release, for the most part, it's saved me
thousands of lines of code. (This is a repost at the request of MS)
I've a scenario I havn't been able to solve with well performing code.
Imagine a model of a phone book, with Companies, Departments and People. A
company has an ID and name, department has an ID and name, and a person has
an ID and name (keeping it simple).
How can I write an entities statement which will return a collections of all
people under a company (regardless of department)?
I considered using something like this:
context.People.Where(p=>p.Department.Company.CompanyId == CompanyIdSearched)
However, my understanding is this goes back to the database to pull the data?
I need to query based on the in-memory data, so I can include data which has
changed but not yet been saved (eg. to check if I've already added a Person
in an import).
What's the most efficient way to query the in-memory context, to get all
People who work for a Company?
Thanks in advance :-)
+M |
|
|
| Back to top |
|
|
|
| Colbert Zhou [MSFT]... |
| Posted: Mon Jun 15, 2009 6:03 am |
|
|
|
Guest
|
Hi Mark,
If the desired colum is not in the Person table, but in another table that
has a 1-n(n-n) relationship with the Person table. The entity framework
needs to load that table into the memory, otherwise it cannot perform the
query. So the question really depends on how we design the database. Based
on my perspective, since the Person can only have one company to work with,
why not record the CompanyID in the Person table. So we can perform the
query like this,
context.People.Where(p=>p.CompanyId == CompanyIdSearched)
What do you think about?
Best regards,
Colbert Zhou
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg at (no spam) microsoft.com. |
|
|
| Back to top |
|
|
|
| Markla... |
| Posted: Mon Jun 15, 2009 6:51 am |
|
|
|
Guest
|
Thanks for your response, although I'm left a little confused why something
like this is impossible in EF without redundant database changes to support
it. The problem with storing the CompanyId in the person table, is you're
working away from a normalised database, putting risk on developers/app
owners. What I read in your response, is that because EF can't handle it, MS
want the developer to compensate by having to write more code + denormalise
the database, soley because EF can't handle it!
So in answer, I think it's a bad idea!
Surely there's a way?
Perhaps do a .Join()?
Perhaps a Linq query which returns a collection of collections, which can
get union'd together?
Something like this perhaps?
IEnumerable<IQueryable<Person>> rss = this.Depts.Select(c =>
c.People.AsQueryable());
if (rss.Count() == 0)
return new Person[0];
List<Person> ret
= rss.Aggregate((workingset, next) =>
workingset.Concat(next)).ToList();
As much as I'm thankful EF has been released, surely there's an efficient
way to do this, without denormalising the database just to compensate for
EF?!?
Thanks,
+M
"Colbert Zhou [MSFT]" wrote:
Quote: Hi Mark,
If the desired colum is not in the Person table, but in another table that
has a 1-n(n-n) relationship with the Person table. The entity framework
needs to load that table into the memory, otherwise it cannot perform the
query. So the question really depends on how we design the database. Based
on my perspective, since the Person can only have one company to work with,
why not record the CompanyID in the Person table. So we can perform the
query like this,
context.People.Where(p=>p.CompanyId == CompanyIdSearched)
What do you think about?
Best regards,
Colbert Zhou
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg at (no spam) microsoft.com.
|
|
|
| Back to top |
|
|
|
| Colbert Zhou [MSFT]... |
| Posted: Thu Jun 25, 2009 11:41 pm |
|
|
|
Guest
|
Hi Mark,
I am very sorry for the late response! I think maybe I misunderstood the
issue here. Now I can see the main concern here is, when we add, modify,
delete some data from the dataContext and do not want to submit changes
immediately, the query will be still performed on the target database
again. So, it will not return the local new added object, right?
Actually, this is how the Entity Framework behaves now, that all query is
performed by the target database. Currently, the only way to get the
in-memory new added, modified, deleted data is using the
ObjectStateManager. Danny from the Entity Framework product group has
created a extension method to do such a in-memory query. He blogs this
topic in the following thread. Please have a read and let me know if this
addresses your main concern,
http://blogs.msdn.com/dsimmons/archive/2009/02/21/local-queries.aspx
Again, sorry for the inconvenience taken to you.
Best regards,
Colbert Zhou
Microsoft Newsgroup Support Team. |
|
|
| Back to top |
|
|
|
| Colbert Zhou [MSFT]... |
| Posted: Mon Jun 29, 2009 8:48 pm |
|
|
|
Guest
|
Hello Mark,
Does the last reply address the issue? If you have any future questions or
concerns, please let me know. I will try my best to research and provide
future support here.
Best regards,
Colbert Zhou
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. |
|
|
| Back to top |
|
|
|
| Markla... |
| Posted: Tue Jun 30, 2009 5:17 am |
|
|
|
Guest
|
Thank you Colbert, that's a good post, it helps solve a few issues, and is
half of my question.
Let me see if I can re-ask the other half clearly, using the original
example again...
What is the most efficient way to construct the code, so I can call
"Companies.People" where the People property returns all people working for a
company (regardless of department)? And without adding a CompanyId to the
Person table.
The approaches I've tried have seemed clumsy and inefficient- I'm hoping
this has been asked before, and there's a great fast approach to write the
code, to return all people for a company. I don't want to have a round-trip
for each department either- I want EF to see that a SQL join is the most
efficient way, and return the results in one query...
Thanks,
+Mark
"Colbert Zhou [MSFT]" wrote:
Quote: Hi Mark,
I am very sorry for the late response! I think maybe I misunderstood the
issue here. Now I can see the main concern here is, when we add, modify,
delete some data from the dataContext and do not want to submit changes
immediately, the query will be still performed on the target database
again. So, it will not return the local new added object, right?
Actually, this is how the Entity Framework behaves now, that all query is
performed by the target database. Currently, the only way to get the
in-memory new added, modified, deleted data is using the
ObjectStateManager. Danny from the Entity Framework product group has
created a extension method to do such a in-memory query. He blogs this
topic in the following thread. Please have a read and let me know if this
addresses your main concern,
http://blogs.msdn.com/dsimmons/archive/2009/02/21/local-queries.aspx
Again, sorry for the inconvenience taken to you.
Best regards,
Colbert Zhou
Microsoft Newsgroup Support Team.
|
|
|
| Back to top |
|
|
|
|
|
All times are GMT - 5 Hours
The time now is Thu Nov 26, 2009 3:59 am
|
|