Get Database Table Name from Entity Framework MetaData

Question

I'm trying to figure out a way to get the underlying SQL table name for a given entity type. I've experimented around with the MetadataWorkspace queries and while I can get lots of information from the object or the storage space, I can't seem to figure out how to map between the two.

So say I have a type in the object model called Lookup - how do I find the tablename (wws_lookups) in the database?

I can query all the EntityType objects for CSpace and SSpace and I can see both listed correctly but I can't figure out how to get SSpace from CSpace.

Is there any way to do this?

Answer 1

No, unfortunately it is impossible using the Metadata APIs to get to the tablename for a given entity.

This is because the Mapping metadata is not public, so there is no way to go from C-Space to S-Space using the EF's APIs.

If you really need to do this you could always build the map yourself by parsing the MSL. This is not for the faint of heart, but it should be possible, unless you are using QueryViews (which are incredibly rare), at which point it is for all intents and purposes impossible (you would have to parse ESQL... argh!)

Alex James

Microsoft.

Answer 2

Alex is right - this is a sad limitation in the Metadata API. I have to just load the MSL as an XML document and do lookups of S-space entities as I process my C-space model.

Answer 3

A possible workaround (not great, but neither are the alternatives...):

var sql = Context.EntitySetName.ToTraceString();

...then parse the SQL, which should be quite simple.

Answer 4

There is a way to delete data using EF without having to load it first I described it in a little more detain in: http://nigelfindlater.blogspot.com/2010/04/how-to-delete-objects-in-ef4-without.html

The trick is to cast the IQueriable into an ObjectQuery and use the ToTraceString method. Then edit the resulting sql string. It works but you need to be careful because you are bypassing the the mechanisms that EF has in place for maintaining dependancies and contraints. But for performance reasons I think it's ok to do this....

have fun...

Nigel...

    private string GetClause<TEntity>(IQueryable<TEntity> clause) where TEntity : class 
    { 
        string snippet = "FROM [dbo].["; 

        string sql = ((ObjectQuery<TEntity>)clause).ToTraceString(); 
        string sqlFirstPart = sql.Substring(sql.IndexOf(snippet)); 

        sqlFirstPart = sqlFirstPart.Replace("AS [Extent1]", ""); 
        sqlFirstPart = sqlFirstPart.Replace("[Extent1].", ""); 

        return sqlFirstPart; 
    } 

   public void DeleteAll<TEntity>(IQueryable<TEntity> clause) where TEntity : class 
    { 
        string sqlClause = GetClause<TEntity>(clause); 
        this.context.ExecuteStoreCommand(string.Format(CultureInfo.InvariantCulture, "DELETE {0}", sqlClause)); 
    }