none
How to get fields exist on table by passing table name by using entityframework core 2 c# ? RRS feed

  • Question

  • How to get fields exist on table by passing table name by using entity framework core 2 ?


    How to get fields exist on table by passing table name ?

    suppose I have table name Employee on SQL server 2012

    and this is fields of Employee Table EmployeeCode,EmployeeName,,..etc

    How to make function pass to it table name and it will retrieve fields or columns as 

    EmployeeCode,

    EmployeeName

    I work on windows form app using entity framework core 2

    structure of function as below

    public List<string> GetColumnNames(string tablename) {

    // how to get columns }


    Sunday, March 10, 2019 3:24 PM

All replies

  • Hi,

    You can use SQL statement in EF to get the columns. Here is the code you can refer to:

        public List<string> GetColumnNames(string tablename)
        {
            using (var db = new TestEntities1())
            {
                var sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = @TableName";
                SqlParameter sqlParameter = new SqlParameter("TableName", tablename);
                var query = db.Database.SqlQuery<string>(sql, sqlParameter).ToList();
                return query;
            }
        }

    Regards,

    Kyle


    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.

    Monday, March 11, 2019 7:39 AM
    Moderator
  • thank you for reply

    it not working 

    it give me compile error 

    Database Facade doesn't contain definition for SQL query and no accessible method SQL query

    error done on this line 

      var query = db.Database.SqlQuery<string>(sql, sqlParameter).ToList();

    on SQL query function 

    I work on asp.net core 2.1

    Monday, March 11, 2019 9:02 AM
  • Hi,

    Did you use "var db = new TestEntities1()"? And update model from database?


    Regards,

    Kyle


    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.

    Monday, March 11, 2019 9:53 AM
    Moderator
  • I work on code first asp.net core 2.1 

    my deb context as following

     public class TabDbContext : DbContext
        {
            public TabDbContext(DbContextOptions<TabDbContext> options)
    : base(options)
            { }
    
            public DbSet<Employee> Employees { get; set; }
    
    ON controller API
    I do as following :
    
     public class EmployeesController : ControllerBase
        {
            private readonly TabDbContext _context;
    
            public EmployeesController(TabDbContext context)
            {
                _context = context;
            }
     public List<string> GetColumnNames(string tablename)
            {
                
                    var sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = @TableName";
                    SqlParameter sqlParameter = new SqlParameter("TableName", tablename);
                    var query = _context.Database.SqlQuery<string>(sql, sqlParameter).ToList();
                    return query;
                
            }



    Monday, March 11, 2019 10:24 AM
  • can any one help me
    Tuesday, March 12, 2019 1:28 AM
  • Hi,

    I create a Code First project via the document "Code First to a New Database". And test the code I provided, it works fine too.

        static void Main(string[] args)
        {
            foreach(var i in GetColumnNames("Blogs"))
            {
                Console.WriteLine(i);
            }
            Console.ReadKey();
        }
        public static List<string> GetColumnNames(string tablename)
        {
            using (var db = new BloggingContext())
            {
                var sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = @TableName";
                SqlParameter sqlParameter = new SqlParameter("TableName", tablename);
                var query = db.Database.SqlQuery<string>(sql, sqlParameter).ToList();
                return query;
            }
        }

    Regards,

    Kyle


    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.

    Wednesday, March 13, 2019 9:39 AM
    Moderator