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

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();

            }

Select allOpen in new window

I attempted to run above I get this error


This  view in VS


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.

I'm now getting

Error	CS5001	Program does not contain a static 'Main' method suitable for an entry point	MoviesDataModel	

Select allOpen 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.

I'm unable to upload a zip file here

I've sent you a link in a msg

Hi
I set  Startup Project & checked reference was ticked


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

Select allOpen 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

 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.

Select allOpen 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);
                }

Select allOpen 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.

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);

Select allOpen 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);

Select allOpen in new window

Apparently

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

Select allOpen in new window

This gives syntax error

ALTER TABLE movie
DROP FOREIGN KEY genreId;

Select allOpen 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'
);

Select allOpen 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--'

Thank you for all your  help

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