C# – List all fields of a table in Entity Data Model

The ADO.NET Data Model alleviates our time when working with database by mapping all database objects into .NET classes object. Instead of writing complex SQL queries with consideration about SQL Injection and tons of security requirments the developer must only concentrate on how they write programming code to accomplish their tasks.
However sometimes it makes us confused when working with “new technique”. For examples how we can list all fields of a table? In traditional way we can just fill a DataTable with a simple query “SELECT * FROM TABLE_NAME” from DataAdapter then run through the columns to list all fields with their data types. But how does it work with Entity Data Model? In the code snippet below I would like to demonstrate how I do it

1. With Entity Data Model

NorthwindEntities nwEntity = new NorthwindEntities();
MetadataWorkspace mdw = ((EntityConnection)nwEntity.Connection).GetMetadataWorkspace();
EntityType tblCustomers = mdw.GetItem<EntityType>("NorthwindModel.Store.Customers", DataSpace.SSpace);
if (tblCustomers != null)
	foreach (var prop in tblCustomers.Members)
		Console.WriteLine(String.Format("MemberName:{0}; Type:{1}", prop.Name, prop.TypeUsage.EdmType.Name));

The identity for function GetItem can be got through editing file Nortwind.edmx with a text editor.

Open with ...


Read EntityType from .edmx

2. With Reflection
As every class in .NET, the mapping classes of Entity Data Model have their own properties and can be accessed through Reflection. Therefore in the 2. code snippet I would like to use Reflection to list all availables properties of a mapping class. However the foreign keys properties and so on are also properties which I do not want to read out.So I use a “bad condition” to filter what are really fields. If you know another way to filter, please tell me.

Type tTemp = (typeof(Customers));
PropertyInfo[] piFields = tTemp.GetProperties(BindingFlags.Public | BindingFlags.Instance );
foreach (PropertyInfo pi in piFields)
	if (!pi.PropertyType.Namespace.Contains("System.Data")) Console.WriteLine(String.Format("MemberName:{0}; Type:{1}", pi.Name, pi.PropertyType));

The image below show results of two methods. It can be easily realized that the data type of 2 methods are different. One is SQL data type and the other is .NET data type

Results of two methods

The complete source code of this blog you can download here “Field List Entity Data Model

Leave a comment

Your email address will not be published. Required fields are marked *