Showing all Content of a database - Show all rows of all entities in EDM model

Question

on my EF6 app i would like to show all the rows of all the Entities currently on my database. Is there a way to do it without having to know what my entities are and what the properties of those entities are? I think using reflection is the way to solve this but i just can´t get to the solution. Any Ideas?

UPDATE: To reply to what people are asking... The database is a SQLSERVER. I don't see how what i want to specifically achive here is relevant. It could even be for the exercise of writing a routine to see all data. As for security reasons, if i for example want to manage the security through the application or give a person admin rights to admin the database around as she pleases, i don't see why it's a design problem. Seen all links posted but all of them so far, solutions include building sql statements which is what i want to get away from.

So as a simple example and to sumarize my pled for help, what i want is through c#, using Entity framework and maybe Linq, be able to write a method that does the "show me everything" command Jasmine talks about. It can be because i decided i dont like the letter "a" and want to purge it from a db. It can be because i want to find all records that contain the word "fido" some where. It can be becouse i want to find how many words/numbers my database has. And like this there is plenty more reasons to what i would want to do this even though like i said i find it irrelevant.

And you do not even know the type of database? (That said, there are ways for most databases, but specific to the type of database) — Mare Infinitus, Feb 03 '15 at 20:15
[This](http://stackoverflow.com/questions/7106108/display-all-data-of-all-tables) could help. — Cheloide, Feb 03 '15 at 20:21
I have needed to do similar things for general purpose viewers and such, but yeah if you're doing this in an application, you probably have a design problem. Same as this question too: http://stackoverflow.com/questions/3892926/entity-framework-get-list-of-tables — Jasmine, Feb 03 '15 at 20:23
@Jasmine This is a bad idea even for general purpose viewers - it essentially defeats the purpose of database security and may expose sensitive data. It's very, very rare that showing all content from all tables is desirable (or even all content from a single table). — xxbbcc, Feb 03 '15 at 20:25
Then what do you think is happening in the SQL Server Management Studio's Object Browser? I don't think the Entity Framework breaks security rules, nor does attempting to enumerate all objects. You're only gonna see the ones you have permission for. And actually "show me everything" is a ridiculously common request. Where have you been? — Jasmine, Feb 03 '15 at 20:30
"Show me all the customers", "show me all the orders"... those I've encountered. I have never once heard "show me all the EVERYTHING", and I certainly wouldn't have thought it would be ridiculously common. — Chris, Feb 04 '15 at 14:27
I fail to see how "without having to know what my entities are and what the properties of those entities are" is possible when using the Entity Framework. — BCdotWEB, Feb 04 '15 at 14:35
It's always possible, with, or without an Entity Framework. If you have an EF model you can use the code in my answer below. If you don't have it, it's a lot harder to do: use SQL Server Management Objects (SMO) to get the database metadata, and then create your own queries. But it's possible (which doesn't mean that it's absolutely absurd, IMHO and in many other people opinion). If you need to do such strange thing you can always script your dadtabase from SSMS and look in the scripts. — JotaBe, Feb 04 '15 at 17:24

JotaBe · Accepted Answer · 2015-02-04T15:35:45.573

The only way to do that is to get all the entities defined in the DbContext, get the Set for each entity, and then serialize the entity (you didn't specify a format) and write it to a file or whereever you want to store the results.

public void GetDbSets(DbContext ctx)
{
  // Initialize, to build the metadata
  ctx.Database.Initialize(true);

  // Access the underlying ObjectContext
  var objectContext = ((IObjectContextAdapter)ctx).ObjectContext;

  // Get the metadata for the DbContext
  MetadataWorkspace mdw = objectContext.MetadataWorkspace;

  // get the assembly, where the entity types are defined
  var asm = ctx.GetType().Assembly;

  // Get the metadata for the entity types
  ReadOnlyCollection<EntityType> entityTypes
    = mdw.GetItems<EntityType>(DataSpace.CSpace);

  foreach (var et in entityTypes)
  {
    var type = asm.GetType(et.FullName);
    Console.WriteLine("{0}: {1}", et.FullName,
      string.Join(", ", et.Members.Select(m => m.Name).ToArray()));
    var dbEntries = ctx.Set(type).AsNoTracking().AsQueryable();
    foreach (var e in dbEntries)
    {
      var propValues = et.Members
        .Select(m => type.GetProperty(m.Name).GetValue(e).ToString())
        .ToArray();
      Console.WriteLine(" {0}", string.Join(", ", propValues));
    }
  }
}

Beware: the code is not optimized. As whenever you use reflection you should cache the PropertyInfos to reuse them, instead of getting them over and over again. This code works, but it's not optimized. For example, for the inner loop you can do this:

var propInfos = new Dictionary<string, PropertyInfo>();
et.Members.ForEach(m => propInfos.Add(m.Name, type.GetProperty(m.Name)));

foreach (var e in dbEntries)
{
    var propValues = et.Members
        .Select(m => propInfos[m.Name].GetValue(e).ToString())
        .ToArray();
    Console.WriteLine(" {0}", string.Join(", ", propValues));
}

However you must decide the serialization format, and the destination, and perhasp you can use some kind of generic serializator, instead of the manual comma separated value that I'm explaining here.

Showing all Content of a database - Show all rows of all entities in EDM model

1 Answers1