C#: How to connect and use a Entity Data Model Database First

trevor1940
trevor1940 used Ask the Experts™
on
Following on from an earlier question

I've created an Entity Framework MoviesDataModel now I'm struggling to work out how to use it I'm pretty sure it's to do with DbContext

I've created a console app in the same folder with the intention of just printing each movie title
Once I've done that I can then explore how to add a filter using LINQ something like

SELECT title from movie where person.name = 'John Wayne';

But first how do I connect and use the MoviesDataModel?

Writing the code bellow I had no intellisense nor does MovieTest appear in Solution Explorer


using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using MoviesModel;

namespace MovieTest
{
    class Program
    {
        static void Main(string[] args)
        {
            List<MoviesDataModel.movie> movies = new List<MoviesDataModel.movie>();

            foreach (MoviesDataModel.movie movie in MoviesEntities)
            {
                Console.WriteLine(movie.title);
            }
        }
    }
}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The "MovieEntities" is the controlling object over the database.  You have to make a new instance of that, and all queries will be based off of it.
It will be IDisposable, so wrap in a using statement.

The nice thing about LINQ too, is until you've actually called .ToArray() or .ToList() or anything else to materialize the query, it won't send anything to SQL, it's just building the sql statement.

So based on the sample above, this is more what you'll need (with a sample of ALL movies, and a stepped example of getting the John Wayne ones):

            using (var context = new MoviesEntities())
            {
                //get a full list of all movies (will return everything from the database)
                var allMovies = context.movies.ToArray();


                //you could do the following 3 in one simple statement, just breaking it out to illustrate how the LINQ objects work

                //get a general queryable object (this has still not sent ANYTHING to sql)
                var movies = context.movies.AsQueryable();

                //now filter based on what you want (this is still just building the target sql command)
                movies = movies.Where(m => m.movielinkpersons.Any(p => p.person.name == "John Wayne"));

                //and now get the list
                var johnWayneList = movies.ToList();

            }

Open in new window

Author

Commented:
I attempted to run above I get this error

error.JPG
This  view in VS

prog1.JPG
is this because MovieTest.Program is listed under Miscellaneous Files?
Nope, that's something totally different.  Right click on the actual project ("MoviesDataModel" in bold in the snapshot above) and go to properties in the menu.
And click on the  Application tab on the left, I'm guessing the "Output Type" is set to Class Library, where it should be set to Console Application.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Author

Commented:
I'm now getting
Error	CS5001	Program does not contain a static 'Main' method suitable for an entry point	MoviesDataModel	

Open in new window


I attempted to add MovieTest.Program to solution Explorer but i'm still unable to set it to the start up program and VS generated multiple errors this leads me to think I created it incorrectly

As I'm likely to want to use the MoviesDataModel in multiple applications can you walk me  through the steps required for creating a new console application or win form ect?

Is this File > New > Project > console application
or
in solution Explorer Right click MoviesDataModel  > Add

Currently my working folder, for this project is

E:\VB\MovieDB\MoviesDataModel

Should additional projects be at the same level

E:\VB\MovieDB\MoviesDataModel\MovieTest

or
 E:\VB\MovieDB\MovieTest
This is really bothering me now, can you zip up the whole solution and attach it here?
I'd really like to see what's going on.

The steps you have are exactly right, except you would do both of them.
File > New > Project > console application
Which gives you the actual project.  THEN you adding the model to the project.
in solution Explorer Right click MoviesDataModel  > Add

For the last part, typically you'd want other projects as per the second one.  One folder for each project in the solution, and the solution in the PARENT folder of the whole thing.
One solution, which is made up of one or more projects (winforms app, console apps, class libraries), and in one project you would have the data model.

Author

Commented:
I'm unable to upload a zip file here

I've sent you a link in a msg
Thanks, got the file.  I fixed up the structure on it, and sent back.
So basically what you end up with is:
Class Library -> containing the data model
Console Application -> running the project

Both should be in the same solution, and the console application needs a REFERENCE to the class library.  That's the step you were missing.

For future, these are more or less the steps to get setup as you wanted (and how the modified solution is now):

Create new project (class library), specify solution name
      - in vs2017, make sure you CHECK "create directory for solution"
      - in vs2019, make sure you UNCHECK "place solution and project in the same directory"

Once in solution:
      - right PROJECT, and add -> New datamodel
      - right click SOLUTION, and add -> New Project (console application)
            - right click console, and pick "Set as Startup Project"
            - right click "References", -> Add Reference -> Projects -> tick off the class library from above


Now the class library has your data model, the console app is set as the startup project, and is linked to the class library.

Author

Commented:
Hi
I set  Startup Project & checked reference was ticked

RefMan.JPG
Built & run the project
Got This error
 
System.InvalidOperationException
  HResult=0x80131509
  Message=No connection string named 'MoviesEntities' could be found in the application config file.
 .....
   at MovieTest.Program.Main(String[] args) in E:\VB\MovieDB\MovieTest\Program.cs:line 27

Open in new window


stackoverflow Suggested to "Try copying the connections string to the .config file"
Unsure why this is required If you reference the data model shouldn't it inherent  the App.config?

I did this and resolved that error

On running again I got a different error

Exception.JPG
 System.Data.Entity.Core.EntityCommandExecutionException
  HResult=0x8013193C
  Message=An error occurred while executing the command definition. See the inner exception for details.
  Source=EntityFramework
  StackTrace:
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at MovieTest.Program.Main(String[] args) in E:\VB\MovieDB\MovieTest\Program.cs:line 39

Inner Exception 1:
SqlException: The data types varchar and text are incompatible in the equal to operator.

Open in new window


Program.cs:line 39
                //and now get the list
                var johnWayneList = movies.ToList();
                foreach(var JWmovie in johnWayneList)
                {
                    Console.WriteLine("John Wayne Film: {0}", JWmovie.title);
                }

Open in new window

Just looked at the project file again, and according the the data model, the person.name field is still a text type.  Looks like you converted most of the rest of the fields to varchar(max), but there's still a couple of text fields.
Text data type really has absolutely no use anymore, this is one of those issues is that it doesn't support a lot of standard sql operators.  In this case the string you pass in for "John Wayne" is implicity a varchar value, which then doesn't compare properly with the text field.

Basically EF just doesn't handle those types well at all (text and image) as they're strongly recommended against.

If you change the columns to varchar(max), and update the context from database again, should resolve the issue.


For the connection string, yes, you do need to have it in app config.  Properties from the first datacontext will be inherited as they're defaulted in code, but when you look at the actual executable folder, you'll only have ONE app.config file, which is from the main one.  Any other settings you need all get read from there.

Author

Commented:
Hi

That makes sense I hadn't made the connection the Inner Exception 1 was at the database level even though it said SqlException DOH!

Anyway I altered the person table by running

ALTER TABLE person ALTER  COLUMN name varchar(MAX);

Open in new window


The only other TEXT column is genre.name I'm unable to alter this  due to foreign key in movie

Tried
ALTER TABLE movie DROP CONSTRAINT genreId;

ALTER TABLE genre ALTER  COLUMN name varchar(MAX);

alter table movie add foreign key (genreId) references genre(id);

Open in new window


Apparently
'genreId' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint.

Open in new window


This gives syntax error
ALTER TABLE movie
DROP FOREIGN KEY genreId;

Open in new window


Hopping I don't have to drop the entire Database and start again?

After  altering person.name in  VB i opened MovieModel.edmx  and Build => Rebuild MovieDataModel

I was then successfully able to run the app and print a list  of John Wayne titles
Great!
And no, you shouldn't have to drop it.  It shouldn't be the FK that's blocking the update, it's the default constraint.

CREATE TABLE genre (
	id INT NOT NULL  IDENTITY  PRIMARY KEY,
	"name"	TEXT NOT NULL DEFAULT 'Unknown'
);

Open in new window


If you run the alter column, it should give you a message like:
The object 'DF__genre__name__xxxxxxx' is dependent on column 'name'.

You just need to drop that default constraint and then change.
alter table genre drop constraint '--whatever the error gives you--'

Author

Commented:
Thank you for all your  help

FYI
I was able to build on your where clause apparently only 18 John Wayne Westerns

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial