There is one-to-many relation between Brand and Campaign entities,
With given Id I need to select Campaign and related Brand entity along with it. TO do so:
public async Task<Campaign> GetAsync(int id)
{
using var dbConnection = _context.CreateConnection();
string query = @"SELECT c.[Id], c.[BrandId], c.[StartDate],
c.[EndDate],b.[Id] FROM [dbo].[Campaign] c
left join [dbo].[Brand] b on c.[BrandId] = b.[Id]
WHERE c.[Id] = @Id";
var campaign = await dbConnection.QueryAsync<Campaign, Brand, Campaign>(query, (campaign, brand) =>
{
campaign.Brand = brand;
return campaign;
}, splitOn: "BrandId", param: new { Id = id });
return campaign.FirstOrDefault();
}
code above not throws exception but child brand entity is not correct.(its dummy record, and BrandId is 0 whereas its valu is 5 in database)
whats missing here?
entity def:
public class Campaign : SqlEntityBase
{
public int BrandId { get; set; }
public DateTime? StartDate { get; set; }
public DateTime? EndDate { get; set; }
public Brand Brand { get; set; }
}
public class Brand : SqlEntityBase
{
public string Name { get; set; }
public List<Campaign> Campaign { get; set; } = new List<Campaign>();
}
Your SQL query doesn't make sense. You're splitting on BrandId in dapper but you never select anything to do with the brand in the query. With your current code, dapper is parsing the SQL column Id into your Campaign POCO (which it cant do because there isn't a property called Id nor have you anything mapped to it in the campaign class).
And then it see's BrandId and then parses everything after that into the Brand POCO, but your remaining columns that you're selecting are the start and end dates for the campaign.
In summary: You need to include the Brand data into the SQL query. You're joining onto the table, but only selecting the campaign data.