Displaying Data from SQL Express db

[JeffInTexas]

NM, I fixed it. Seems I had to just reload the revised database by adding new data source. If anyone is inclined, I'd like to know how to avoid this from happening again. Was it because I failed to include a db refresh on stored procedures in C# wizard?


I used this simple loop to show me my table data:

using (var dbContext = new TestEntities())
{
var names = from Name in dbContext.Clients
select Name;
foreach (var name in names)
{
MessageBox.Show(name.Name + " " + name.Address);
}

}

Then, I updated the table in SQL Studio to add an Id column, and I added an Invoices table to the db. I also set primary keys, indexes and a relation, all in Server Studio.

I refreshed my database in VS C# with the wizard. The text editor recognizes my new tables and columns, but the above code no longer works.

Here's what I think might be relevant in the error message detail:

Message=
Model1.msl(6,10) : error 3002: Problem in mapping fragments starting at line 6:Potential runtime violation of table Clients's keys (Clients.Id): Columns (Clients.Id) are mapped to EntitySet Clients's properties (Clients.Id) on the conceptual side but they do not form the EntitySet's key properties (Clients.Id, Clients.Name).


Here's the full error message detail:

System.Data.EntityCommandCompilationException was unhandled
Message=An error occurred while preparing the command definition. See the inner exception for details.
Source=System.Data.Entity
StackTrace:
at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
at System.Data.EntityClient.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
at System.Data.EntityClient.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
at System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)
at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Span span, ReadOnlyCollection`1 compiledQueryParameters)
at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at WindowsFormsApplication1.Form1..ctor() in C:\Users\Jeff\documents\visual studio 2010\Projects\WindowsFormsApplication2\WindowsFormsApplication2\Form1.cs:line 31
at WindowsFormsApplication1.Program.Main() in C:\Users\Jeff\documents\visual studio 2010\Projects\WindowsFormsApplication2\WindowsFormsApplication2\Program.cs:line 18
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: System.Data.MappingException
Message=
Model1.msl(6,10) : error 3002: Problem in mapping fragments starting at line 6:Potential runtime violation of table Clients's keys (Clients.Id): Columns (Clients.Id) are mapped to EntitySet Clients's properties (Clients.Id) on the conceptual side but they do not form the EntitySet's key properties (Clients.Id, Clients.Name).

Source=System.Data.Entity
StackTrace:
at System.Data.Mapping.StorageMappingItemCollection.ViewDictionary.SerializedGenerateViews(EntityContainer container, Dictionary`2 resultDictionary)
at System.Data.Mapping.StorageMappingItemCollection.ViewDictionary.SerializedGetGeneratedViews(EntityContainer container)
at System.Data.Common.Utils.Memoizer`2.<>c__DisplayClass2.<Evaluate>b__0()
at System.Data.Common.Utils.Memoizer`2.Result.GetValue()
at System.Data.Common.Utils.Memoizer`2.Evaluate(TArg arg)
at System.Data.Mapping.StorageMappingItemCollection.ViewDictionary.GetGeneratedView(EntitySetBase extent, MetadataWorkspace workspace, StorageMappingItemCollection storageMappingItemCollection)
at System.Data.Metadata.Edm.MetadataWorkspace.GetGeneratedView(EntitySetBase extent)
at System.Data.Query.PlanCompiler.PreProcessor.ExpandView(Node node, ScanTableOp scanTableOp, IsOfOp& typeFilter)
at System.Data.Query.PlanCompiler.PreProcessor.ProcessScanTable(Node scanTableNode, ScanTableOp scanTableOp, IsOfOp& typeFilter)
at System.Data.Query.PlanCompiler.PreProcessor.Visit(ScanTableOp op, Node n)
at System.Data.Query.InternalTrees.ScanTableOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Query.PlanCompiler.SubqueryTrackingVisitor.VisitChildren(Node n)
at System.Data.Query.PlanCompiler.SubqueryTrackingVisitor.VisitRelOpDefault(RelOp op, Node n)
at System.Data.Query.PlanCompiler.PreProcessor.Visit(ProjectOp op, Node n)
at System.Data.Query.InternalTrees.ProjectOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Query.PlanCompiler.SubqueryTrackingVisitor.VisitChildren(Node n)
at System.Data.Query.PlanCompiler.SubqueryTrackingVisitor.VisitRelOpDefault(RelOp op, Node n)
at System.Data.Query.PlanCompiler.PreProcessor.Visit(ProjectOp op, Node n)
at System.Data.Query.InternalTrees.ProjectOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Query.PlanCompiler.SubqueryTrackingVisitor.VisitChildren(Node n)
at System.Data.Query.InternalTrees.BasicOpVisitorOfNode.VisitDefault(Node n)
at System.Data.Query.InternalTrees.BasicOpVisitorOfNode.VisitPhysicalOpDefault(PhysicalOp op, Node n)
at System.Data.Query.InternalTrees.BasicOpVisitorOfT`1.Visit(PhysicalProjectOp op, Node n)
at System.Data.Query.InternalTrees.PhysicalProjectOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Query.PlanCompiler.PreProcessor.Process()
at System.Data.Query.PlanCompiler.PreProcessor.Process(PlanCompiler planCompilerState, StructuredTypeInfo& typeInfo)
at System.Data.Query.PlanCompiler.PlanCompiler.Compile(List`1& providerCommands, ColumnMap& resultColumnMap, Int32& columnCount, Set`1& entitySets)
at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
InnerException:

[Silent Sojourner]

No way. Entity framework is not that smart to figure out any change you manually made on your database.

[JeffInTexas]

I thought "refreshing" the database in the wizard means that the wizard goes out and gets all the changes made to your data structure which might have been made elsewhere, like in Server Studio. If not, then what does "refresh" do?

[JeffInTexas]

It looks like I am getting the hang of something here:

dbConnection.Open();
dbDataAdapter=new SqlDataAdapter(sql,dbConnection);
dbDataSet = new DataSet();
dbDataAdapter.Fill(dbDataSet, "Clients");
DataRowCollection clientRows = dbDataSet.Tables["Clients"].Rows;

foreach(DataRow client in clientRows)
{
string cName = client["Name"].ToString();
MessageBox.Show(cName);
}

My question: Is this Entity Framework usage, or something else? If so, what?

Also, I don't like much having to refer to tables and columns by strings within quotes. Intellisense does not pick them up, and so it is more burdensome and more error prone to have to do this.

Is there a way to accomplish the same thing I did above where Intellisense will offer me my table names and columns?

Thanks.... (at least I feel I understand now about Data Adapters, Data Sets and Data Rows).

[JeffInTexas]

Update. A few hours more of digging, and I think I have found that I was using the older LINQ method.

So, now I get it, I think. That is, LINQ depends on datasets and dataadapters, whereas entity is all in entities. Moreover, entity is supported by intellisense.

And so, I go back to using the entity method as follows:

TestEntities1 dbContext = new TestEntities1();
var names = from Name in dbContext.Clients
select Name;
foreach (var name in names)
{
MessageBox.Show(name.Name);
}

I am getting a crash on the foreach(), whereas it used to work before. The TestEntities1() still exists in my data sources.

This is the crash report:

error 3002: Problem in mapping fragments starting at line 15:Potential runtime violation of table Invoices's keys (Invoices.InvoiceNumber): Columns (Invoices.InvoiceNumber) are mapped to EntitySet Invoices's properties (Invoices.InvoiceNumber) on the conceptual side but they do not form the EntitySet's key properties (Invoices.ClientId, Invoices.InvoiceNumber).\r\n"}

Obviously, something's out of whack. Refresh in the DataBase Explorer does not help. How do you fix this? I bet I could use the Data Source wizard and load the whole thing again, which will be TestEntities2, but it seems like this is twice I have somehow broken my Entity map. How come I keep breaking it?