none
How can I distinguish Database Table and View in T4 script RRS feed

  • Question

  • I'm in a situation that I need to generate different code for Database Table and View  (DB first here).

    And I found that I'll get both mapping entities for Tables and Views if I use a foreach like this:

    foreach (EntityType entity in ItemCollection.GetItems<EntityType>().OrderBy(e => e.Name))

    {}


    so here comes the question, is there any class under Namespace System.Data.Metadata.Edm from which I could only get mapping entities for Database Table or Views? Thanks a lot!

    • Moved by Caillen Monday, January 13, 2014 9:37 AM EF related question.
    Saturday, January 11, 2014 3:20 PM

Answers

  • Hello,

    >>Running transformation: System.InvalidOperationException: The space 'SSpace' has no associated collection.

    Do you add a line like below in the codes?

    db.Orders.ToTraceString();

    I find that in the second try script, it is missing. Please add it, because we need it to load metadata.

    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 Haobo Ding Wednesday, January 22, 2014 1:13 PM
    Wednesday, January 22, 2014 8:05 AM
    Moderator

All replies

  • Could anybody give me some help? Or should I modify the type of the question?
    Monday, January 13, 2014 6:50 AM
  • Try to tweak from the link http://www.codeproject.com/Articles/23053/View-Database-Structure-Using-C

    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    Monday, January 13, 2014 9:19 AM
  • Hello Haobo Ding,

    >> is there any class under Namespace System.Data.Metadata.Edm from which I could only get mapping entities for Database Table or Views?

    This is not possible, however, we can do a filter operation on these EntitySet, information about view is stored in SSpace in EntitySet's MetadataProperties. If we are working in CSpace to generate classes we don't have access to this information because entity from CSpace has no knowledge about its storage. Because API for CSSpace is not public it is also quite hard to move from CSpace space to SSpace without using some naming convention (or hardcoded solution) to convert name of the entity to name of the table.

    A workaround should be like below:

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    MetadataWorkspace mw = db.MetadataWorkspace;
    
                    db.Orders.ToTraceString();
    
                    EntityContainer entityContainer = mw.GetItems<EntityContainer>(DataSpace.SSpace).Single();
    
                    IEnumerable<EntityType> tables = mw.GetItems<EntityType>(DataSpace.SSpace);
    
    
    
                    foreach (EntityType entityType in tables)
    
                    {
    
                        EntitySet entitySet = entityContainer.GetEntitySetByName(entityType.Name, true);
    
                        string type = entitySet.MetadataProperties["http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator:Type"].Value.ToString();
    
    
                        if (type.ToUpper() == "Tables".ToUpper())
    
                        {
    
                            Console.WriteLine(entityType.Name + " is table");
    
                        }
    
                        else
    
                        {
    
                            Console.WriteLine(entityType.Name + " is view");
    
                        }
    
                    }
    
    
                    Console.ReadLine();
    
                }
    

    The result is:

    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.

    Tuesday, January 14, 2014 3:02 AM
    Moderator
  • Thanks a lot, I'll try to go trough it and figure out the way to implement it later my daytime.
    Tuesday, January 14, 2014 3:12 PM
  • Thanks a lot, I'll try to go trough it and test your code in my solution later my daytime.
    Tuesday, January 14, 2014 3:13 PM
  • Hello Haobo Ding,

    >> is there any class under Namespace System.Data.Metadata.Edm from which I could only get mapping entities for Database Table or Views?

    This is not possible, however, we can do a filter operation on these EntitySet, information about view is stored in SSpace in EntitySet's MetadataProperties. If we are working in CSpace to generate classes we don't have access to this information because entity from CSpace has no knowledge about its storage. Because API for CSSpace is not public it is also quite hard to move from CSpace space to SSpace without using some naming convention (or hardcoded solution) to convert name of the entity to name of the table.

    A workaround should be like below:

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    MetadataWorkspace mw = db.MetadataWorkspace;
    
                    db.Orders.ToTraceString();
    
                    EntityContainer entityContainer = mw.GetItems<EntityContainer>(DataSpace.SSpace).Single();
    
                    IEnumerable<EntityType> tables = mw.GetItems<EntityType>(DataSpace.SSpace);
    
    
    
                    foreach (EntityType entityType in tables)
    
                    {
    
                        EntitySet entitySet = entityContainer.GetEntitySetByName(entityType.Name, true);
    
                        string type = entitySet.MetadataProperties["http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator:Type"].Value.ToString();
    
    
                        if (type.ToUpper() == "Tables".ToUpper())
    
                        {
    
                            Console.WriteLine(entityType.Name + " is table");
    
                        }
    
                        else
    
                        {
    
                            Console.WriteLine(entityType.Name + " is view");
    
                        }
    
                    }
    
    
                    Console.ReadLine();
    
                }

    The result is:

    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.

    I got this error while saving T4 file:                      

    Running transformation: System.ArgumentException: The member with identity 'Contact' does not exist in the metadata collection.
    Parameter name: identity ...

    'Contact' is just one table in my database. I'm sure I've added this table into my edmx file.

    Wednesday, January 15, 2014 1:24 PM
  • Try to tweak from the link http://www.codeproject.com/Articles/23053/View-Database-Structure-Using-C

    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    Thanks for your reply, I've downloaded the source code and gone through it, but it's not using Entity Framework, seems not what I'm looking for, thank you all the same
    Wednesday, January 15, 2014 1:37 PM
  • Hello Haobo Ding,

    >> is there any class under Namespace System.Data.Metadata.Edm from which I could only get mapping entities for Database Table or Views?

    This is not possible, however, we can do a filter operation on these EntitySet, information about view is stored in SSpace in EntitySet's MetadataProperties. If we are working in CSpace to generate classes we don't have access to this information because entity from CSpace has no knowledge about its storage. Because API for CSSpace is not public it is also quite hard to move from CSpace space to SSpace without using some naming convention (or hardcoded solution) to convert name of the entity to name of the table.

    A workaround should be like below:

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    MetadataWorkspace mw = db.MetadataWorkspace;
    
                    db.Orders.ToTraceString();
    
                    EntityContainer entityContainer = mw.GetItems<EntityContainer>(DataSpace.SSpace).Single();
    
                    IEnumerable<EntityType> tables = mw.GetItems<EntityType>(DataSpace.SSpace);
    
    
    
                    foreach (EntityType entityType in tables)
    
                    {
    
                        EntitySet entitySet = entityContainer.GetEntitySetByName(entityType.Name, true);
    
                        string type = entitySet.MetadataProperties["http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator:Type"].Value.ToString();
    
    
                        if (type.ToUpper() == "Tables".ToUpper())
    
                        {
    
                            Console.WriteLine(entityType.Name + " is table");
    
                        }
    
                        else
    
                        {
    
                            Console.WriteLine(entityType.Name + " is view");
    
                        }
    
                    }
    
    
                    Console.ReadLine();
    
                }

    The result is:

    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.

    I got this error while saving T4 file:                      

    Running transformation: System.ArgumentException: The member with identity 'Contact' does not exist in the metadata collection.
    Parameter name: identity ...

    'Contact' is just one table in my database. I'm sure I've added this table into my edmx file.

    It turns out 'Contacts' works well, in other words, I passed the EntityName not the EntitySetName to function GetEntitySetByName()

    But here comes another error:

    Running transformation: System.ArgumentException: The member with identity 'http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator:Type' does not exist in the metadata collection.

    And the error disappear if I comment this line of code:

    string type = entitySet.MetadataProperties["http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator:Type"].Value.ToString();

    Wednesday, January 15, 2014 2:58 PM
  • Looks lie there is still some problem for me to implement this, could you please give some further help? Thanks a lot!
    Wednesday, January 15, 2014 3:44 PM
  • What is the program environment? I use VS2012, .Net Framework4.5, EF5 and SQLServer2012.

    Could you please upload the error program to skydriver?


    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, January 16, 2014 8:55 AM
    Moderator
  • What is the program environment? I use VS2012, .Net Framework4.5, EF5 and SQLServer2012.

    Could you please upload the error program to skydriver?


    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.

    Sorry, I find that there are difference starting from here :

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    {
        MetadataWorkspace mw = db.MetadataWorkspace;
        db.Orders.ToTraceString();
        EntityContainer entityContainer = mw.GetItems<EntityContainer>(DataSpace.SSpace).Single();
    I found the root cause, and here comes another question: how can I add using into .tt file? Cause I kept getting error like the name of "XXXXXXEntities" can not be found, are you missing any using etc. So I tried to google it that how to add using my namespace into the T4 script, could you please give me some further help? Thanks a lot!
    Thursday, January 16, 2014 2:11 PM
  • >>add using my namespace into the T4 script

    There are two T4 script, XXXX.Context.tt and XXXX.tt.

    If you want to add additional namespace to XXXX.Context.tt, find line 43 and add additional namespace like below:

    using System;
    
    using System.Data.Entity;
    
    using System.Data.Entity.Infrastructure;
    
    using yournamespce;// add additional namespace
    

    If you want to add additional namespace to XXXX.tt, find UsingDirectives method and change its context with below:

    return inHeader == string.IsNullOrEmpty(_code.VsNamespaceSuggestion())
    
                ? string.Format(
    
                    CultureInfo.InvariantCulture,
    
                    "{0}using System;{1}" +
    
                    "{2}" +
    
                                                                    "{3}",
    
                    inHeader ? Environment.NewLine : "",
    
                    includeCollections ? (Environment.NewLine + "using System.Collections.Generic;") : "",
    
                    inHeader ? "" : Environment.NewLine,
    
                                                                    true ? (Environment.NewLine + "using yournamespace;") : "")
    
                : "";
    
    Friday, January 17, 2014 9:55 AM
    Moderator
  • >>add using my namespace into the T4 script

    There are two T4 script, XXXX.Context.tt and XXXX.tt.

    If you want to add additional namespace to XXXX.Context.tt, find line 43 and add additional namespace like below:

    using System;
    
    using System.Data.Entity;
    
    using System.Data.Entity.Infrastructure;
    
    using yournamespce;// add additional namespace

    If you want to add additional namespace to XXXX.tt, find UsingDirectives method and change its context with below:

    return inHeader == string.IsNullOrEmpty(_code.VsNamespaceSuggestion())
    
                ? string.Format(
    
                    CultureInfo.InvariantCulture,
    
                    "{0}using System;{1}" +
    
                    "{2}" +
    
                                                                    "{3}",
    
                    inHeader ? Environment.NewLine : "",
    
                    includeCollections ? (Environment.NewLine + "using System.Collections.Generic;") : "",
    
                    inHeader ? "" : Environment.NewLine,
    
                                                                    true ? (Environment.NewLine + "using yournamespace;") : "")
    
                : "";

    I'm afraid this could not solve my problem, my key problem is that "DataBaseFirstDBEntities db = new DataBaseFirstDBEntities()" will throw error if I couldn't add using to my assembly into the T4 script.

    I also did some research, found that it seems this could work: "<#@ assembly name="$(SolutionDir)\

    \MyProject\bin\Debug\mylibrary.dll" #>", but it just lead to another problem:


    The assembly $(SolutionDir)\MyProject\bin\Debug\mylibrary.dll could not be loaded.

    There was an exception during load: A dependency could not be found!

    just similar to this question:

    http://stackoverflow.com/questions/15439047/how-do-i-see-what-dependency-failed-to-load

    Here I'd like to describe some for my situation:

    1. I'm using VS2013, .NetFramework 4.0, EntityFramework 4.0;
    2. my T4 script is written by myself, not  XXXX.Context.tt and XXXX.tt. added from VS
    3. I need to use my ObejctContext class in this T4 script written by me, need to add using assembly of current project in my T4 script (the T4 and ObjectContext are in the same project)

    Hope you could give me some further help, thanks very much!

    Saturday, January 18, 2014 4:20 AM
  • Since we do not know the exact T4 written by you, we cannot reproduce this error.

    Cloud you please provide a simple sample so that we reproduce it and to check why it throws such error.


    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.

    Monday, January 20, 2014 9:55 AM
    Moderator
  • Since we do not know the exact T4 written by you, we cannot reproduce this error.

    Cloud you please provide a simple sample so that we reproduce it and to check why it throws such error.


    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.

    I've uploaded 3 T4 script here:

    https://skydrive.live.com/redir?resid=D6DDD6C7D262DA6B!276&authkey=!AKirZRjey5rytcU&ithint=folder%2c.tt

    The first one is T4WorksWell.tt, it works well, I use it to generate one Repository Class file for each entity, the only problem is that I could distinguish the entity is a table or view in it, just generate the same repository for all tables and views.

    The second one is T4FirstTry.tt, I made small chages based on the first one, just hitting error under the comment //Here throw the error

    The third one is T4SecondTry.tt, I made big changes in my script according to your code, just encount the can not find class or namespace error, you may refer to my comment //Can not find Class or NameSapce "DataBaseFirstDBEntities"//Replace the "DataBaseFirstDBEntities" with my own in the same project doesn't work, either

    You may test the T4 scripts in your project, Entity Framework 4 and .Net Framework 4, thanks a lot!

    Monday, January 20, 2014 1:39 PM
  • Hi,

    Maybe I misunderstand, I watch your T4 script and find that you want to do the determine operation in the T4 script, what I think is you will do it in codes.

    It needs use to reference the DLL where the DataBaseEntities object is located.

    To reference assembly in T4 template in VS2010 you have some options:

    1.GAC your assemblies and use Namespace Reference or Fully Qualified Type Name

    2.Use a hard-coded Fully Qualified UNC path

    3.Copy assembly to Visual Studio "Public Assemblies Folder" and use Namespace Reference or Fully Qualified Type Name.

    4.Use or Define a Windows Environment Variable to build a Fully Qualified UNC path.

    5.Use a Visual Studio Macro to build a Fully Qualified UNC path.

    For details, please check link below:

    http://stackoverflow.com/questions/3434713/cant-reference-an-assembly-in-a-t4-template

    Regards.
    Tuesday, January 21, 2014 10:30 AM
    Moderator
  • Hi,

    Maybe I misunderstand, I watch your T4 script and find that you want to do the determine operation in the T4 script, what I think is you will do it in codes.

    It needs use to reference the DLL where the DataBaseEntities object is located.

    To reference assembly in T4 template in VS2010 you have some options:

    1.GAC your assemblies and use Namespace Reference or Fully Qualified Type Name

    2.Use a hard-coded Fully Qualified UNC path

    3.Copy assembly to Visual Studio "Public Assemblies Folder" and use Namespace Reference or Fully Qualified Type Name.

    4.Use or Define a Windows Environment Variable to build a Fully Qualified UNC path.

    5.Use a Visual Studio Macro to build a Fully Qualified UNC path.

    For details, please check link below:

    http://stackoverflow.com/questions/3434713/cant-reference-an-assembly-in-a-t4-template

    Regards.

    Thanks for your kindly help, I could successfully use my Object class in my T4 script now.

    But now, the line of code :

    EntityContainer entityContainer = mw.GetItems<EntityContainer>(DataSpace.SSpace).Single();

    throw this Error: 

    Running transformation: System.InvalidOperationException: The space 'SSpace' has no associated collection.

    I've updated my Object class and edmx file to my skydrive space (https://skydrive.live.com/redir?resid=D6DDD6C7D262DA6B!276&authkey=!AKirZRjey5rytcU&ithint=folder%2c.tt), could you please give me some further help? Thanks a lot!

    Tuesday, January 21, 2014 4:23 PM
  • Hello,

    >>Running transformation: System.InvalidOperationException: The space 'SSpace' has no associated collection.

    Do you add a line like below in the codes?

    db.Orders.ToTraceString();

    I find that in the second try script, it is missing. Please add it, because we need it to load metadata.

    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 Haobo Ding Wednesday, January 22, 2014 1:13 PM
    Wednesday, January 22, 2014 8:05 AM
    Moderator
  • Hello,

    >>Running transformation: System.InvalidOperationException: The space 'SSpace' has no associated collection.

    Do you add a line like below in the codes?

    db.Orders.ToTraceString();

    I find that in the second try script, it is missing. Please add it, because we need it to load metadata.

    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.

    Thanks for your once and once again help and patient explanation.

    BTW, there is no definition for "Orders" in my ObjectContextclass, I tried to replace it with any entityset in my ObjectContext, it did works, thanks a lot!

    Wednesday, January 22, 2014 1:16 PM
  • Hi Fred,

    I've been studying and using Entity Framework 6 now, the solution you given to me works on EF 4, but kind of not work so well on EF 6, do you have any better suggestion for the same senario on EF 6?

    Thanks a lot!

    Tuesday, August 18, 2015 5:39 PM