0

How can I get the type of each column in a SQL Server table or view using Entity Framework?

I need to do this BEFORE I get all the data from the table, because I want to allow users to filter, for example, from a date before the data is actually retrieved from the SQL Server table/view.

So if I had a table of Books with a publish date, I would like to return each column (Name, Publisher, Publish Date) type in order to allow filtering beforehand. I need this code to do this because I will not necessarily know the columns, since the user may use several different tables.

The .NET Framework type is fine, I don't need the SQL Server type...

Here's some example code:

using (var ArgoEntities = new ARGOEntities())
{
    //find the types here before the user performs the query so i can build the below code
    var query = from b in ArgoEntities.tbl_Books
                where b.PublishDate>[user specified date]  //some date here the user enters
                select b;

    var book = query.First();
}

EDIT: I can do this so far only by getting the first record in the table, like this...

      using (ARGOEntities ArgoEntities = new ARGOEntities())
        {
            //var fred = typeof(ARGOEntities).GetProperties();
            //var fred = ArgoEntities.GetType().GetProperties();

            var a=ArgoEntities.tbl_Books.FirstOrDefault();
            var b = ObjectContext.GetObjectType(a.GetType());
            var c=b.GetProperties();
        }

but i repeat, I DON'T want to get any records first.

Theodosius Von Richthofen
  • 2,441
  • 1
  • 21
  • 46
  • In EF, see Example 6: http://www.scip.be/index.php?Page=ArticlesNET24. It is much simpler to do in plain ADO.NET. – NoChance Jan 22 '15 at 22:36
  • 3
    You shouldn't even need to to be honest. Because in EF you have model's representing all of your data that is going to come back.. So you can just typeof(SomeModel) then use Type.GetProperties() for properties tagged with the EF Column attribute and you'll have all the properties and their type for what's getting populated form sql. – Ryan Mann Jan 22 '15 at 22:39
  • See also SQL Server Management Objects (https://msdn.microsoft.com/en-us/library/ms162557.aspx) for the situation where you really do want to examine tables and columns and you don't have an EF model for them. – Ian Mercer Jan 23 '15 at 06:14
  • @Ryios can you provide an example then? I don't know what SomeModel is, please use my example. – Theodosius Von Richthofen Jan 23 '15 at 16:35

2 Answers2

1

you can use the GetProperty and then PropertyType:

using (var ArgoEntities = new ARGOEntities())
        {
            //find the types here before the user performs the query so i can build the below code
            //Like this you can retrieve the types:
            foreach (string propertyName in ArgoEntities.CurrentValues.PropertyNames)
            {
                   var propertyInfo = ArgoEntities.Entity.GetType().GetProperty(propertyName);
                   var propertyType = propertyInfo.PropertyType;

            }
            //
            var query = from b in ArgoEntities.tbl_Books
                        where b.PublishDate>[user specified date]  //some date here the user enters
                        select b;

            var book = query.First();
        }
void
  • 7,760
  • 3
  • 25
  • 43
  • thanks. the ArgoEntities.CurrentValues.PropertyNames throws an error stating CurrentValues is not defined and there is no extension method CurrentValues. do i need to add reference or somehting? – Theodosius Von Richthofen Jan 23 '15 at 16:08
  • i would assume i would need to at least specify the Table or something before i can get eachfield's properties in the table... – Theodosius Von Richthofen Jan 23 '15 at 16:43
  • That's what we are trying to explain... The Model directly represents a table.. EF maps all of the tables in your Database to a class in your data layer. So in a simple scenario, ArgoEntities could be to a table called ArgoEntities... E.g. in my project I have a Table called People that maps to a class called Person.. I don't have to tell it the table ever, because Person was created for the People table. So if I get the properties on the Person class, they directly represent columns on the People table. It's just automatic. – Ryan Mann Jan 25 '15 at 09:16
0

Ryios' comment lead me to the extremely simple answer I knew it had to be, which will give me an array of PropertyInfo for each field in the table.

var columns=typeof(tbl_Books).GetProperties();
Theodosius Von Richthofen
  • 2,441
  • 1
  • 21
  • 46
  • I've posted an alternate solution [here](http://stackoverflow.com/a/28640169/722393), in case you want to use EF Metadata. It's certainly not as simple as yours, but sometimes Reflection isn't an option. – InteXX Feb 20 '15 at 23:48