var data = await dbContext.Set<OwnerData>().FromSqlRaw(@"
SELECT [OWP].[OwnerProfileId],
SELECT [OWP].[Email],
FROM [user].[OwnerProfile] AS [OWP]
CAST(ISNULL([OWB].[CustomBalance], 0) AS decimal(18, 3)) AS [CustomBalance]
INNER JOIN [user].[OwnerBalance] AS [OWB] ON [OWB].[OwnerProfileId] = [OWP].[OwnerProfileId]
WHERE [ThirdPartyRefId] = {0}", ownerProfileId)
.ToListAsync();
I rewrite this into linq expression like this
var data = await _context.Set<OwnerProfile>()
.Include(x => x.OwnerBalances)
.Where(x => x.ThirdPartyRefId== ownerProfileId)
.ToListAsync();
not sure how to set this
CAST(ISNULL([OWB].[CustomBalance], 0) AS decimal(18, 3)) AS [CustomBalance]
into lambda query
Without having class definitions its a little difficult to give you specifics, but you could try to add something like:
var data = await _context.Set<OwnerProfile>()
.Include(x => x.OwnerBalances)
.Where(x => x.ThirdPartyRefId== ownerProfileId)
.Select(x => new
{
x.Prop1,
x.Prop2,
CustomerBalance = x.CustomerBalance.Value != null ? x.CustomerBalance.Value : 0
})
.ToListAsync();
You may be able to move the select statement outside the query too:
var newData = data.Select(x => new
{
x.Prop1,
x.Prop2,
CustomerBalance = x.CustomerBalance.Value != null ? x.CustomerBalance.Value : 0
})
You might be able to use conditional access in the 2nd version, but possibly not in the first.