locked
How to create user defined table type through model by using code first approach? RRS feed

  • Question

  • Hello world :),

    I just started to learn entity framework. Following http://msdn.microsoft.com/en-in/data/jj193542.aspx, i created a sample project that will create two tables (Blog and Post). Then add one record to Blog. Below is the structure of the tables.

    Complete of the sample console app is below

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data.Entity;
    
    namespace CodeFirstNewDatabaseSample
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db = new BloggingContext())
                {
                    bool dbCreated = db.Database.CreateIfNotExists();
                    if (dbCreated)
                        Console.WriteLine("Created a new database...");
    
                    Console.WriteLine("Enter a name for a new blog: ");
                    string name = Console.ReadLine();
                    var blog = new Blog { Name = name };
                    db.Blogs.Add(blog);
                    db.SaveChanges();
    
                    var query = from x in db.Blogs
                                orderby x.Name
                                select x;
    
                    Console.WriteLine("All blogs in the database: ");
                    foreach (var currBlog in query)
                    {
                        Console.WriteLine(currBlog.Name);
                    }
                }
    
                Console.WriteLine("Press any KEY to exit.");
                Console.ReadKey();
            }
        }
    
        public class Blog
        {
            public int BlogId { get; set; }
    
            public string Name { get; set; }
    
            public List<Post> Posts { get; set; }
        }
    
        public class Post
        {
            public int PostId { get; set; }
    
            public string Title { get; set; }
    
            public string Content { get; set; }
    
            public int BlogId { get; set; }
            public virtual Blog Blog { get; set; }
        }
    
        public class BloggingContext : DbContext
        {
            public BloggingContext() :
                base(@"name=CodeFirstSampleConnStr")
            {
    
            }
    
            public DbSet<Post> Posts { get; set; }
            public DbSet<Blog> Blogs { get; set; }
        }
    }

    Considering my next application that i am going to develop, i will need to do bulk insert. So for that i will need to use table type at sql server end. For example, for Blogs table i will need a table type "BlogsType" for inserting 10000 records.

    As we can see in above code snippet, i am creating a database and adding tables to it at run time. Likewise, i will need to create a type like below

    CREATE TABLE BlogsType AS TYPE (

    // All the columns should be inherited from Blogs table only along with datatype, length, etc.

    // I am not sure what complications may come like IDENTITY may not work with table type.

    ) GO

    How do i create a table type using the code first approach where i will need to create it from model?

    Appreciate your help.


    Thanks, Pratap

    Forgot to mention one point.

    I will also need to create a stored procedure as below

    CREATE PROCEDURE sp_AddManyBlogs
    @AddBlogs_TVP BlogsType READONLY
    AS
     INSERT INTO Blogs
    ([Id], [Name])
    SELECT * FROM @AddBlogs_TVP;
    GO

    • Edited by Pratap15 Wednesday, December 18, 2013 4:36 PM Forgot to add few points
    Wednesday, December 18, 2013 4:32 PM

Answers

  • Hi

    I think better way is to skip the Entity Framework entirely for this operation and rely on SqlBulkCopy class. Other operations can continue using EF as before if you do not want to use the user define table type.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Pratap15 Thursday, December 26, 2013 1:16 PM
    Wednesday, December 25, 2013 2:50 AM

All replies

  • Hello,

    >>How do i create a table type using the code first approach where i will need to create it from model?

    Do you mean that you want create the user define table type using code first just like creating the table by POCO class?

    If it is, unfortunately, this is impossible, we have to define the user define table type at the database end.

    If you define the type at database end and want to know how to use the table type with code first, for this, in the program we need to define a dbcontext extension method like below:

    public class BlogContext : DbContext
    
        {
    
    
            public DbSet<Blog> Blogs { get; set; }
    
    
            public DbSet<Post> Posts { get; set; }
    
        }
    
    
        public static class DbContextExtension
    
        {
    
            // Extension method of DbContext object
    
            public static void ExecuteStoreProcedure(this DbContext @this, string storeProcName, params object[] parameters)
    
            {
    
                string command = "EXEC " + storeProcName + " @Paramtable";
    
                @this.Database.ExecuteSqlCommand(command, parameters);
    
            }
    
        }
    
    } 
    

    In main method, we call it like below:

    using (BlogContext db = new BlogContext())
    
                {
    
                    //db.Database.Create();
    
    
                    DataTable dt = new DataTable();
    
                    dt.Columns.Add("Name");
    
                    dt.Rows.Add("Test1");
    
                    dt.Rows.Add("Test2");
    
                    dt.Rows.Add("Test3");
    
                    dt.Rows.Add("Test4");
    
                    dt.Rows.Add("Test5");
    
    
                    var items = new SqlParameter("Paramtable", SqlDbType.Structured);
    
                    items.Value = dt;
    
                    items.TypeName = "dbo.BlogsType";
    
                    db.ExecuteStoreProcedure("sp_AddManyBlogs", items);
    
                }
    

    >> I am not sure what complications may come like IDENTITY may not work with table type.

    The identity type will work with table type, so we do not need to add the “Id” column in both DataTable and store procedure.

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 19, 2013 4:55 AM
  • Hello Fred,

    Sorry for late reply.

    I want to create the table type just like we create tables using POCO classes. Since it is not possible, i am looking for an alternate and efficient way for performing bulk insert and bulk update in entity framework.


    Thanks, Pratap

    Tuesday, December 24, 2013 11:49 AM
  • Hi

    I think better way is to skip the Entity Framework entirely for this operation and rely on SqlBulkCopy class. Other operations can continue using EF as before if you do not want to use the user define table type.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Pratap15 Thursday, December 26, 2013 1:16 PM
    Wednesday, December 25, 2013 2:50 AM