Issue using LINQ to SQL with POCO entities

Currently I am working on a sample project in .net 4.0 and in which I am using Entity Model to expose my Data from SQL Server 2008 database.
Now I am also using POCO entities as in .Net 4.0 POCO entities gives us better control over the entities we are using to map with the entities in the Entity Model compare to the auto generated entities generated by Entity Model itself.
Now here are my two POCO entities ‘User’ and ‘Account’….

Click the image for full view…….

Now I am querying data from SQL using LINQ to SQL as shown below in code…….

This code should return the ‘Account’ with users in it but instead it returns no data in browser or no Error/ Exception as well but simply shows “Webpage can’t be displayed” in IE8 as shown in the image below…..

Now I just tried to figure out the issue but not found anything as there was no error or Exception in the code and the only symptom of this issue was that when I was debugging the code it comes to the debug point twice in the method where I am using LINQ to SQL. The good thing was it was showing the correct results inside the variable ‘accountList’ but was not showing any result in the browser.

Then I suddenly notice that my ‘Account’ entity has the property ‘Users’ of type [List of ‘User’] (Angle brackets not allowed here) and then again each User has the property ‘Account’ and again each ‘Account’ has List and seems like infinite dependency on each other….

So what I tried for the solution was, I just added the attribute [IgnoreDataMember] (using System.Runtime.Serialization assembly) on ‘Account’ property in my ‘User’ entity class as shown below in image……..

And tested it again…. and guess what…. Bingoooo…. It just simply works and now browser shows the correct data as shown in image below……

Click the image for full view…….

So what I guess is that it was happening because of kind of infinite circular dependency created by data model I am using.

(If I am not using extention method ‘Include()’ in my LINQ to SQL, it works just fine and returns the Accounts without ‘Users’ off course)

But the question that still remains is, it’s the known issue or the unexplored bug of using LINQ to SQL with POCO entities.

Working with ADO.Net Entity Framework 4.0

I am using ADO.Net Enity Framework 4.0 in my current sample learning project (WCF REST Service Application) and I am also using POCO entities to map objects that are in Entity Model.

As I am working with N-tier architecture, I am having my POCO entities, Entity Model and my executable service page(start up page) in separate projects. My ObjectContext class (for mapping entities in entity model and POCO entities) is in project with POCO entities.

Issue :
Now as I tried to run my service project and navigate to “http://localhost/PronerveEPMS/PronerveRestService/Accounts” for getting all “Accounts” in my database, my code raised the Exception “The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.”. See the figure below……..

Click the image for full view

Solution :
Now the issue was my connection string of Entity Model was in app.config file(by default) with Entity Model and so when I was executing my service project it used to search connection string in web.config file of service project and so not found it.
And so as a solution i just put my default connection string of Entity Model in web.config of service project and also need to add reference of the project containing Entity Model in executable service project.

ok, now searching for the solution to this issue I found that many developers were searching for the exact connection string to use with entity model and so here is my connection string….

<add name="pronerveEntities"
connectionString="metadata=res://pronerve.data.sql/pronerve.csdl|
res://pronerve.data.sql/pronerve.ssdl|
res://pronerve.data.sql/pronerve.msl;
provider=System.Data.SqlClient;
provider connection string=’Data Source=NIKHILTHAKER-PC;
Initial Catalog=pronerve;
User ID=sa;
Password=xyz;
MultipleActiveResultSets=True’;”
providerName=”System.Data.EntityClient” />

Here “metadata” part of the connection string points to the auto generated csdl, ssdl, msl files which contains the schema and other information about the database used by Entity Model.

Second thing is ‘pronerve.data.sql’ (bold sections in connection string), which is the name of my project that contains Entity Model, can be replaced by simple ‘*’ but it is always reccomended that you always use the proper assembly name in such scenarios. For more details on this, just go to here… http://msdn.microsoft.com/en-us/library/cc716756.aspx

For more on ADO.Net Entity Framework please go to, http://msdn.microsoft.com/en-us/data/aa937723.aspx

I will also talk more about POCO entities in .Net 4.0 in some other blog.