Last post Sep 25, 2014 09:28 AM by v2kea60
Sep 18, 2014 05:11 AM|v2kea60|LINK
Given: A very poorly designed database with no foreign keys. When using DB first, this problem means that EF will NOT have a single navigation property for all auto-generated entities.
The developers of this project did not use EF, rather they were writing what I'd call ad-hoc SQL Queries where each web page in old application contained any query it needed for that page only. SQL really doesn't require foreign keys as a multi-table query
in it's simplest form can use complex where clauses like this:
Select TableA.Something, TableB.Something
from TableA, TableB,
where TableA.ID = TABLEB.SomeNonEnforcedTableAKeyValue
This database is on a separate machine than the MVC project web site, I call this the back-end and have no problems connecting, and getting around the foreign key issue by extending EF entity models to include fields (due to no navigation properties). NOTE:
There is no way I can fix the foreign key issue as the DB schema is out of my control.
Today I was thinking about the possibility of allowing the web clients of this application to host a client side DB, where I would fix the foreign key issues allowing me to focus on 1) Getting the data from the back end and in essence putting it into the
properly designed client side database.
My questions are: 1) Is this idea possible? 2) It is a good or bad idea? 3) Are there client side restrictions due to browser security issues and the limiting of what the client can access locally? My thoughts are we could get around the browser side security
issues if I have the ability to separately run an installer of SQL Express and pre-configure the local side SQL DBs first. After all it would just be another database. So in essence, the client side DB data would all be treated as temp data.
Sep 18, 2014 05:19 AM|ignatandrei|LINK
1) Is this idea possible?
You can make a DB and add FK.
2) It is a good or bad idea?
Depends. How do you deal with dual sending data( from server to local and from local to server) ?
Tables that clients modify are with columns PK based on identity ? ( in this case , it is rather difficult)
Do you plan using SqlServer features to replicate data?
Are there client side restrictions due to browser security issues and the limiting of what the client can access locally?
If locally means it has a IIS and a database then No.
After all it would just be another database.
Another databaseS - one for each client. Synchornization will occur.
Sep 18, 2014 05:27 AM|AidyF|LINK
You're confusing Entity Framework and MVC, EF has nothing to do with MVC, they are totally separate technologies. If your database isn't suitable for use with EF then just use ado.net instead. Way simpler than trying to run client-side databases, and when
(if) you do manage to sort the db schema you just move the code to EF later.
Sep 18, 2014 08:59 AM|v2kea60|LINK
Actually, no, I'm not confusing MVC and EF. I'm trying to overcome a db schema issue and it just so happens the application is MVC. Your advice of just use ADO.NET is too simplistic and not relavant to the problem I'm solving now, nor the question I asked.
You are assuming I've not looked into that option as well as I don't know the difference between EF and MVC. Once again your reponse to worthless to me.
I have been investigating some other options since posting this question. One of the things I found out is that the EF model will allow relationships to be created that do not exist in the DB. I will test this out today to see if this is true and how it
applies to this issue. If it works then I know that my MVC application can use POCO objects and ensure ChangeTracking and FIX-UP logic are automatically implemented.
Sep 18, 2014 09:35 AM|AidyF|LINK
Your problem isn't MVC related though so you shouldn't post in the MVC forum, it is EF related so should post in the data access forum, or somewhere else more suitable. It helps keep the forum sections focused and relevant. If you think my advice was "simplistic"
and of no relevance then please feel free to ignore it :) no need to post saying it was worthless. You'll find it far easier in the long-run to use the appropriate technology for your situation, and while EF can most certainly have relationships that don't
exist in the DB (EF is a mapping technology, it maps your database domain to your application domain and is capable of many fairly impressive feats that means your database and your app domain don't require any form of 1 to 1 mapping and don't need to resemble
each other), but you might still struggle to get it fully working without proper keys defined. An issue you won't have with ado.net.
It's probably the route I'd take, but if you want to persevere with EF then you're free to do so.
Sep 25, 2014 09:28 AM|v2kea60|LINK
For anyone reading this post, here's a recap of the problem and solution:
Problem: You are using EF and the DB you use as the MODEL has no foreign key relationships, but there are tons of queries doing all kinds of where clauses across tables, joins and other multi-table work. You want to know how to project results into a EF
Class that is not a part of the model based on the database first mappings.
Solution: You have to inherit base classes and either use composition techniques to expose the other table properties or just add the new fields a properties manually to the models. If you decide you want to create anonymous types then you are forever stuck
with the problem of knowing the field names for the dynamic results returned. Anonymous projections will work but it will be difficult to maintain over the years. It's my opinion, where possible, to choose strongly typed models every chance you get.