Custom model in FromSql method

[scaki]

Describe what is not working as expected.

I want execute sql query with EFCore. In the old version, there was a sqlquery method, which can be used to define the query result to a special model. I have the FromSql method in my version and I can't use this feature. The query that I want the sum of rows comes up in its own model and I can't reach the count column. How do I resolve this issue?

Steps to reproduce

      RawSqlString query = "SELECT  C.*, Impression.Count AS 'ImpressionCount', Interaction.Count AS 'InteractionCount' FROM Campaign C LEFT JOIN (SELECT Im.CampaignId,Count(Im.Id) AS 'Count' FROM Impression Im GROUP BY Im.CampaignId) Impression ON C.Id=Impression.CampaignId LEFT JOIN (SELECT Inte.CampaignId,Count(Inte.id) AS 'Count' FROM Interaction Inte GROUP BY Inte.CampaignId) Interaction ON C.Id=Interaction.CampaignId WHERE C.UserId=@UserId";
      var UserId = new SqlParameter("UserId", userId);
      var result = this.MssqlContext.Campaign.FromSql(query, UserId).AsNoTracking().ToList();

Further technical details

EF Core version: 2.2.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: VSCode

[ajcvickers]

@scaki Currently this requires defining an entity type with the expected shape (but which does not have to have a key) in the model. See #1862

Issue #10753 is tracking support for this without first defining the shape in the model.

[scaki]

I've tried this solution before, but my Campaign model doesn't have ImpressionCount and InteractionCount fields. In fact, these fields do not exist in the database. For this reason, I get the error that the ImpressionCount and InteractionCount fields do not exist in contex. These fields are completely virtual. What I need is to give the result of the query directly to me without equalizing any model.

[ajcvickers]

@scaki Please post a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing so we can investigate.

[scaki]

Repository

      RawSqlString query = "SELECT  C.*, Impression.Count AS 'ImpressionCount', Interaction.Count AS 'InteractionCount' FROM Campaign C LEFT JOIN (SELECT Im.CampaignId,Count(Im.Id) AS 'Count' FROM Impression Im GROUP BY Im.CampaignId) Impression ON C.Id=Impression.CampaignId LEFT JOIN (SELECT Inte.CampaignId,Count(Inte.id) AS 'Count' FROM Interaction Inte GROUP BY Inte.CampaignId) Interaction ON C.Id=Interaction.CampaignId WHERE C.UserId=@UserId";
      var UserId = new SqlParameter("UserId", userId);
      var result = this.MssqlContext.Set<Campaign>().FromSql(query, UserId).Select(t => new CampaignWithImpressionAndInteraction{ImpressionCount = t.ImpressionCount, InteractionCount = t.InteractionCount}).ToList();
      return result;

Campaign Model

public partial class Campaign
    {
        public Campaign()
        {
            Impression = new HashSet<Impression>();
            Interaction = new HashSet<Interaction>();
        }

        public int Id { get; set; }
        public int? UserId { get; set; }
        public string Name { get; set; }
        public int? DomainId { get; set; }
        public int? CampaignGoalId { get; set; }
        public string JsonData { get; set; }
        public bool? Status { get; set; }
        public DateTime? CreatedAt { get; set; }
        public DateTime? UpdatedAt { get; set; }
        public DateTime? DeletedAt { get; set; }

        public virtual CampaignGoal CampaignGoal { get; set; }
        public virtual Domain Domain { get; set; }
        public virtual User User { get; set; }
        public virtual ICollection<Impression> Impression { get; set; }
        public virtual ICollection<Interaction> Interaction { get; set; }
    }

CampaignWithImpressionAndInteraction

public partial class CampaignWithImpressionAndInteraction : Campaign
  {
    public int? ImpressionCount { get; set; }
    public int? InteractionCount { get; set; }
  }

Error

'Campaign' does not contain a definition of 'ImpressionCount', and no accessible 'ImpressionCount' expansion methods found accepting a first argument of type 'Campaign' could be missing (may your use directive or assembly reference be missing?) (CS1061) [Repository]

[karanjilka]

+1 Having the same issue

[smitpatel]

That's just invalid C# query.

[scaki]

@smitpatel yes, i ask how can i do valid query. How can i fix this problem

[ajcvickers]

@scaki The call to FromSql needs to use the type you defined:

this.MssqlContext.Set<CampaignWithImpressionAndInteraction>().FromSql...

[scaki]

I've tried this code before, it doesn't work. I solved the problem by adding the CampaignWithImpressionAndInteraction model to MssqlContext.