Entity Framework - Mixing model-first and database-first approaches?

Question

For my first project using Entity Framework, I chose to use the "model-first" approach wherein I design my entities from which a script will be created for generating the required database tables. This has worked quite well until I ran into a situation where I couldn't quite retrieve all the data elements I am needing in a single query (my LINQ skills are still limited).

Since I can easily write the query I need in SQL, I was wondering whether it would be possible to write a view for my database and then generate an entity in my model from it, in other words, mixing the two model/database approaches. Any ideas on this?

Not out of the box, but there are third party tools that allow you to mix both models. One tool that can do that (bring individual changes across selectively) is the EFv4 'Model Comparer' in my add-in which you can download and get a trial license for at huagati.com/dbmltools . This blog post describes the model comparer in more detail (with a screencast video showing the basic features): huagati.blogspot.com/2010/07/… — KristoferA
– KristoferA, Commented Mar 1, 2011 at 9:45

Ladislav Mrnka · Accepted Answer · 2011-03-01 08:42:52Z

1

You can't mix model-first and db-first. Once you manually modify database you can't use Generate database from EDMX any more or you delete your changes performed directly in DB.

In some cases it can be avoided by downloading Entity Designer Database Generation Power Pack extension to Visual Studio 2010. When using this extension together with VS 2010 Premium or Ultimate you can use additional DB generation workflows and T4 templates which are able to use VS tools to compare newly generated DB with existing DB and create only ALTER scripts.

But still this will most probably not work with DB views because information about DB view is stored in SSDL (storage model description). Model-first doesn't use Views and every time you regenerate database it creates table instead of view.

So if you want to run arbitrary SQL query use ExecuteStoreQuery (only EF4) or give up with model first.

answered Mar 1, 2011 at 8:42

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PongGod Over a year ago

Thanks, it looks like using the ExecuteStoreQuery() with a custom class will do the trick for me.

Chris Sainty · Accepted Answer · 2011-02-28 22:43:52Z

1

Are you aware of the SqlQuery() method?

ctx.Listings.SqlQuery("SQL Query Here", p1, p2...)

answered Feb 28, 2011 at 22:43

Chris Sainty

9,3461 gold badge29 silver badges31 bronze badges

5 Comments

Chris Sainty Over a year ago

Yes it is. It will run a custom query that returns rows for your table (in my case the Listings table) and then maps them into objects with all the change tacking etc turned on.

PongGod Over a year ago

Do you have a more detailed example to show? So far I have not been able to find any information about this SqlQuery() method. What namespace does it belong to?

Chris Sainty Over a year ago

It is part of the DbSet<TEntity> class. So no namespace or anything needed, it is not an extension method. http://blogs.msdn.com/b/adonet/archive/2010/12/06/ef-feature-ctp5-released.aspx

Chris Sainty Over a year ago

Actually I may have mixed up Model First and Code First. Sorry. This method is part of the new Code First CTP bits for EF.

Ladislav Mrnka Over a year ago

This method also exists in common EF but it is called ObjectContext.ExecuteStoreQuery.

Collectives™ on Stack Overflow

Entity Framework - Mixing model-first and database-first approaches?

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related