Databases
StackOverflow2013
Postsdbo
POMerge Two or More Entity Framework Entities with Dynamic Columns?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMerge Two or More Entity Framework Entities with Dynamic Columns?
    primarykey
    data
    text
    <p>I've got a project that uses an SQLite database with Entity Framework and VB.Net. The database is complex but everything is set up well for accessing the data in individual tables. </p> <p>What I need is a way to transform (pivot?) the data from two or more tables and get a resulting table with columns that are generated dynamically based on the data in the original tables. As an example, if I start with two tables that look like this:</p> <pre><code>Table 1 Id Data Field 1 17 1 1 34 2 1 72 3 2 23 1 2 11 2 2 46 3 3 1 1 3 19 2 3 24 3 Table 2 Id Data Field 1 abc 4 1 def 5 1 ghi 6 2 jkl 4 2 mno 5 2 pqr 6 3 stu 4 3 vwx 5 3 yz 6 </code></pre> <p>I need to end up with the following as the resulting table:</p> <pre><code>Id Field1 Field2 Field3 Field4 Field5 Field6 1 17 34 72 abc def ghi 2 23 11 46 jkl mno pqr 3 1 19 24 stu vwx yz </code></pre> <p>Simplified, obviously, but I hope the general layout is clear. At design time I do <strong>not</strong> know how many fields (and thus columns in the resulting merged table) there will be. I do know, however, that all the Data in Table 1 will be numeric and all the Data in Table 2 will be text.</p> <p>Obviously I can loop through the data and construct a DataTable dynamically in code, but I'm trying to figure out if there's a more efficient way to do it, that can be used with data binding to a datagrid. I've tried various LINQ Joins (using System.Linq.Dynamic) but I always run into problems with the fact that I don't know how many of the fields there will be.</p> <p>Another important factor is that this data will need to be editable, so when the user updates a value I'll need to have some way to figure out which row in which original table is being edited.</p> <p>Does anybody have any ideas?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndrew M
    1. USAndrew M
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POMerge Two or More Entity Framework Entities with Dynamic Columns?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSo many duplicates! [Here](http://stackoverflow.com/questions/6160817/pivot-in-c-sharp-entity-framework-3-5), [here](http://stackoverflow.com/questions/11267982/entity-framework-5-and-pivot-table-how), [here](http://stackoverflow.com/questions/8082057/how-to-use-pivot-table-with-entityframework) and counting. Many without answers because the simple conclusion is: it can't be done in any quick standard way.
      singulars
      1. POMerge Two or More Entity Framework Entities with Dynamic Columns?
      1. USGert Arnold
    2. COThey don't seem to be asking quite the same thing - those questions seem to be related to a more simple pivot, but I'm looking to merge _and_ modify the structure of the tables in a dynamic way at runtime, not just display them in a different way.
      singulars
      1. POMerge Two or More Entity Framework Entities with Dynamic Columns?
      1. USAndrew M
    3. COOK. Since you add the entity-framework tag it seems you want to do the pivot by an EF query. If you intend to do it in any other way maybe you'd like to explain how EF is related? Anticipating on this I can say that you won't be able to use EF to address the dynamic table. Building the table should probably be done in a stored procedure. Saving data is a matter of tracking row key + column name back to the original record, probably in a stored procedure again.
      singulars
      1. POMerge Two or More Entity Framework Entities with Dynamic Columns?
      1. USGert Arnold
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload