locked
How to get column names of db table in using EF RRS feed

  • Question

  • i have found this below linq query....tell me does it give all column name of table ?

    what is UserQuery ?

    from t in typeof(UserQuery).GetProperties()
    where t.Name == "tablename"
    from c in t.GetValue(this,null).GetType().GetGenericArguments()[0].GetFields()
    select c.Name

    share some best code.

    Friday, February 9, 2018 12:09 PM

Answers

  • Hi Sudip_inn,

    >>i have found this below linq query....tell me does it give all column name of table ? what is UserQuery ?

    According to your description and related code, I am sure what is UserQuery, it seems that it is model class which map the database table structure.

    But you could retrieve related column name via SQL statement. like this:

     using (var db = new EFDemoContext())
    {
        var columnNames = db.Database.SqlQuery<string>("SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('tablename');").ToList();
    }
    

    You could also get all column name via Model class, like this:

    using (var db = new EFDemoContext())
    {
         var type = typeof(Product);
    
         var metadata = ((IObjectContextAdapter)db).ObjectContext.MetadataWorkspace;
    
         // Get the part of the model that contains info about the actual CLR types
         var objectItemCollection = ((ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace));
    
         // Get the entity type from the model that maps to the CLR type
        var entityType = metadata
                            .GetItems<EntityType>(DataSpace.OSpace)
                                  .Single(e => objectItemCollection.GetClrType(e) == type);
    
        // Get the entity set that uses this entity type
        var entitySet = metadata
                        .GetItems<EntityContainer>(DataSpace.CSpace)
                              .Single()
                              .EntitySets
                              .Single(s => s.ElementType.Name == entityType.Name);
    
         // Find the mapping between conceptual and storage model for this entity set
         var mapping = metadata.GetItems<EntityContainerMapping>(DataSpace.CSSpace)
                                  .Single()
                                  .EntitySetMappings
                                  .Single(s => s.EntitySet == entitySet);
    
        // Find all properties (column) that are mapped
         var columnName = mapping
                        .EntityTypeMappings.Single()
                        .Fragments.Single()
                        .PropertyMappings
                        .OfType<ScalarPropertyMapping>()
                        .Select(t => t.Property.Name).ToList();
                    
    
    }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Monday, February 12, 2018 9:14 AM
    Monday, February 12, 2018 2:52 AM