CODEWITHSUNDEEP
.netentity-frameworkasp.net-coreentity-framework-core
Ameerudheen.K
Ameerudheen.K

Reputation: 667

Dynamic type in Dbcontext property

I have a stored procedure to fetch data from a complex query result. So to get data from that procedure I have added a property in Dbcontext with its result type.

 internal DbQuery<Dashboard> Dashboard { get; set; }

Here I have defined the Dashboard model myself.

When the result columns from procedure are static, it's working fine, but the issue is sometimes result set from procedure may have dynamic columns. So I need to change my Dashboard model also accordingly. How can I manage this by adding a dynamic type while declaring my Dbcontext property?

I have just tried it with another way

Currently I have a set of static data which is

IQueryable<DynamicDashboard> fullJoinedResult = context.DynamicDashboard.FromSql("dbo.DynamicDashboard").DefaultIfEmpty().AsQueryable();

Now for each of the dynamic field I needed iam doing a left join of my current static data with the dynamic table data

I have stored my dynamic fields in this list

var selectedDynamicFields = new List<DashboardFields>(); 


var proposalDynamicDatas = context.ProposalDynamicData.Where(w => selectedDynamicFields.Select(s => s.FormFieldId).Contains(w.FormFieldId)).ToList();

foreach (var field in selectedDynamicFields)
  {
    var qry = resultSet.GroupJoin(proposalDynamicDatas.Where(w=>w.FormFieldId=field.FieldId).AsEnumerable(), 
    "ProposalId", "ProposalId", "new(outer.ProposalId as Foo,inner.ProposalId as 
    Bars)");                          
  };

Can I dynamicallly change my result set inside this for loop? currenlty iam getting error in GroupJoin , its saying No 'ProposalId' in IEnumerable. Cant find out that

Upvotes: 0

Views: 829

Answers (1)

Steve Py
Steve Py

Reputation: 34738

Honestly I wouldn't use EF for that specific requirement. EF is an ORM to map relational data to an object model and back. While it can interact with stored procedures, that is not where it shines or how you should try and standardize a data layer that way to try and suit 100% of your cases.

When building a house you need to attach wood together and the common choices are nails and screws. There are pros and cons to each, where EF might be considered a good clutched power drill for screwing a frame together. That does not preclude that everything in the house should be screwed together. Some jobs might call for nails or nuts & bolts. Use the best tool for the job at hand rather than trying to figure out how to screw everything together. (Consistency for consistency's sake is a code/design smell in my book.)

From what you describe you want to store a dynamic view of data to present as a dashboard. The ORM models can handle the specific views for the reviewing/editing the objects in question, but to get out a dynamic amalgamation of data for a dashboard, that is more like a reporting consideration where a different technique would be suited to populate such a read-only model. If you have a stored procedure capable of returning the mishmash of data then consider using a vanilla ADO call to return an iterate-able structure to populate your view, including the IDs you might need to later use EF to retrieve and populate the respective whole objects. As long as the solution is easy to understand and self reliant/contained, it gets the job done better than trying to force a square peg through a round hole.

Edit: If you have an existing static Dashboard object with values and want to change that to something that accommodates adding/interchanging a dynamic set of values from various related tables:then one option would be to return a relational structure where the dynamic values are returned as something like a Name-Value pair. (I.e. SourceTable, SourceColumn, SourceRowId, SourceType, Value )

Having the dynamic Sproc/View return a Dashboard entity that contains child elements with the above values that your business logic can inspect to assist with displaying and linking back to their respective tables. SourceTable and SourceRowId would allow the business logic to determine which entity and row a value came from. SourceColumn would give you either a display name for the value or a known constant to look up a suitable display name. SourceType would help with possible client-side display options as the Value would likely need to be returned as string. So for instance a returned DateTime might be always returned in ISO format (yyyy-MM-ddThh:mm:ssZ) and then the UI/BL could format that according to the user's region/preferences. The same could be used to identify Currency values to show appropriate symbols, etc.

In this way, EF could still function fine in a read-only capacity to retrieve this structure for a Dashboard and related DashboardValues collection.

Upvotes: 1

Related Questions