2
\$\begingroup\$

I have these two queries and they work just fine. I use the values they retrieve to populate a title for a view in my application. It seems though that I should be able to accomplish this with one query and I do not know how.

Dim firstName = (From row In db.ItemDetails
    Where row.Unique_Location_ID = locationID And row.Item_Type_Identifier = 1 And _
    row.Category_Attribute_Identifier = 2
    Select CStr(row.Value)).FirstOrDefault()

Dim lastName = (From row In db.ItemDetails
    Where row.Unique_Location_ID = locationID And row.Item_Type_Identifier = 1 _
    And row.Category_Attribute_Identifier = 3
    Select CStr(row.Value)).FirstOrDefault()

ViewBag.assignedTo = ("Employee this item is currently assigned to: " + firstName + " " + lastName + ".")

Is there a better way to do this?

\$\endgroup\$
3
\$\begingroup\$

You can achieve this by using the group by clause to create an aggregated list constrained by Unique_Location_ID and Item_Type_Identifier. To make the query more readable, store the names in local variables using the let clause.

Dim name As String = (
    From row In (From r In db.ItemDetails Where r.Unique_Location_ID = locationID And r.Item_Type_Identifier = 1 Select r)
    Group row By row.Unique_Location_ID Into rows = Group
    Let firstName = (From row In rows Where row.Category_Attribute_Identifier = 2 Select CStr(row.Value)).FirstOrDefault()
    Let lastName = (From row In rows Where row.Category_Attribute_Identifier = 3 Select CStr(row.Value)).FirstOrDefault()
    Select String.Concat(firstName, " ", lastName).Trim()
).FirstOrDefault()

I also advice you to not use underscores in public members as suggested in the .net general naming conventions. So, if possible, rename the members to UniqueLocationID, ItemTypeIdentifier and CategoryAttributeIdentifier.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.