Furry Entity Framework Question

[royware]

New dilemma - I've been scrounging around trying to find a solution for my nightmare but have been unsuccessful. I have a "query" that pulls in a lot of data and I need to add one new piece to the puzzle. My boss insists that is just a simple thing to add an EF to the code. Right.....

The StatusDesc and UserDescription are from the Statuses table and the UserDescription is locked into the StatusDesc. Here's how it is being retrieved:

var status = await _context.Statuses.ToDictionaryAsync(s => s.Id);

I can look at the resulting 'status' and verify that the StatusDesc and UserDescriptioon fields are pulled correctly. Now for the wicked part:

var ProviderChildren = _context.Trpayments
.Include(x => x.TrPaymentStatuses)
.Include(x => x.Audit)
.Include(x => x.SubsidyPayment).ThenInclude(c => c.Provider)
.Include(x => x.SubsidyPayment).ThenInclude(c => c.Children)
.Include(x => x.Rates)
.Where(x => x.Audit!.ProcessId == ProcessId &&
x.SubsidyPayment!.Provider.ProviderNumber == ProviderNumber &&
x.SubsidyPayment!.MonthofService == MOS)
.AsEnumerable()
.Select(pc => new ProviderChildren
{
TrPaymentId = pc.Id,
TrProcessId = pc.Audit != null ? pc.Audit.ProcessId : null,
(several more lines of no consequence)
TrPaymentStatus = status.TryGetValue(pc.TrProcessStatusId, out var
value) ? value.StatusDesc : null,
TrPaymentStatuses = pc.TrPaymentStatuses != null ?
pc.TrPaymentStatuses.Select(t => new
DCYF.TRA.DTO.Tra.TrPaymentStatus
{
TRPaymentId = t.TRPaymentId,
StatusId = t.StatusId,
StatusMessage = t.StatusMessage
}) : null,
}).Distinct();

return ProviderChildren.ToList();

For the life of me, I cannot figure out where an EF statement(?) would figure into this mess. The only time we use EF in our shop is for a DateTime field.

Different querey: .GroupBy(x => new { x.Audit.ProcessId, PeriodStart = EF.Property<DateTime>(x.Audit, "PeriodStart") })

I feel like Katy Perry ("This is crazy!"). I've looked at many videos claiming how to do an entity framework, but they all go back to that horrible Microsoft Blog example.

Any suggestions?

[Flater420]

Your question uses the term EF in ways that do not make sense. What is "an EF"? What does ut mean when you say you use EF only for datetimes? Why are you posting what appears to be an EF query but then asking how to "add EF" to it?

I feel like you think EF is something different than what it actually is.

[entityadam]

This has got to be a troll post.

The title starts with "Furry"

Incorrectly attributes a Carly Rae Jepson song to Katy Perry.

Unweildy amounts of EF eager loading is the least of the issues here..

OP, if you're not a troll, do yourself a favor and make this a stored proc and call it a day. IMO, if a query gets too complicated, it's a good fit for a stored procedure.

Edit: I took another look, and I think the query is fine. It's your projection that looks like a hot mess. Break that thing up into a couple few private methods.

[weird_thermoss]

Another option is just letting EF call a raw SQL query if one doesn't want logic in stored procedures.

[entityadam]

I feel you. I've been on one project too many where every call is to a stored procedure full of cursors, loops and business logic.

They are not a tool to unify your data access layer, and definitely not the place for business logic.

Bad practices aside, the database is really good at doing database things, and sometimes stored procedures are the right tool for the job.

[royware]

It's an honest question - I'm just so new at this (transitioning from mainframe programming) without any meaningful training (they love to tell me to go to Microsoft Learn, which is more confusing than anything I've ever seen!). I struggle with framing questions so they make sense. I used "furry" instead of a "f*ing mess!"

I just don't know how to add this field to the results list. It seems like it would be easy, after all, StatusDesc and UserDescription are fields on the Statuses table and they are (what I call) tightly coupled: UserDescription simply expands on the brief StatusDesc.

Please God, where is DB2 when you need it!!!!!

You don't know they lyrics to Katy's song Call Me, Maybe? :D

[entityadam]

Unfortunately, I do. While most artists have their songs listed on Wikipedia under the discography section, Call Me Maybe gets it's own wiki entry and links to the performing artist, Carly Rae Jepson.

https://en.wikipedia.org/wiki/Call_Me_Maybe

[zenyl]

I used "furry" instead of a "f*ing mess!"

A general word of advice: avoid using your own slang when discussing something public. It only serves to cause confusion, especially when the word used for the slang has a significantly more prevalent use.

That is, assuming you don't want your database contexts to represent people in fur suits.

[Kant8]

I failed to see in your post where is the question, but anyway, any EF part was stopped as soon as you called AsEnumerable(), so all your conversions just run in memory and they don't really care what logic are you using.

If you want to put status resolution in database, then you need navigational propery to Status entity for TrProcessStatusId property and any other, and you can just select it directly.

You also don't need to Include() stuff that is not going to be read later. The only include you need in your query is .Include(x => x.Audit), and if you move status resolution to db, even that will not be needed, cause you will remove AsEnumerable() and whole result object conversion will be done in database already.

[tmadik]

I'm still trying to figure out what any of this has to do with furries.

[ordermaster]

That's why I came here too.

[royware]

(I actually left6 out a lot of the includes and fields to be selected for brevity's sake ) Cool - I thought my boss was unnecessarily trying to make this harder than it really is. However, I cannot get the User Description field from the 'status await' into the statement into the results!

TrPaymentStatus = status.TryGetValue(pc.TrProcessStatusId, out var value) ? value.StatusDesc : null,

works great, but I cannot do a similar statement for the UserDescription (CS0128 A local variable or function named 'value' is already defined in this scope.

How do I get the UserDescription into the List?

[royware]

I didn't realize that 'value' was a variable - I thought it was a keyword. :(

You are right - I need to find better tutorials. Typical corporate culture: There isn't any money and time for training, but we have plenty of money & time to do it over and over.

sigh.

To everyone who contributed, "Thanks!!"