1

I am converting an existing application and I am using the existing database structure.

This is the model of the data I am querying:

public partial class StaffNotes
{
        public int RecordNumber { get; set; }
        public double? ContactNumber { get; set; }
        public double? WriteNumber { get; set; }
        public double? ToStaff { get; set; }
        public double? FromStaff { get; set; }
        public string Note { get; set; }
        public DateTime? CriticalDate { get; set; }
        public DateTime? NoteDate { get; set; }
        public int? TicketNumber { get; set; }
        public bool Imp { get; set; }
}

Instead of displaying the number of the ToStaff, I would like to display the appropriate name.

I have written the following query:

var staffNotes = _context.StaffNotes.FromSql("Select s.record_number, s.contact_number, s.To_Staff, s.From_Staff, s.Note_Date, s.Ticket_Number, s.Imp, s.Critical_Date, S.Note, s.write_number, c.first_name, c.last_name from staffnotes s, contacts c where s.contact_number = c.contact_number and s.Contact_Number = " + id).ToList();

My code runs and I'm not getting any errors. The only issue is the first_name and last_name do not appear in the data.

Is this because it is not part of the model? Is there a way to get around this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brett Davis
  • 152
  • 1
  • 14
  • Does this answer your question? [Raw SQL Query without DbSet - Entity Framework Core](https://stackoverflow.com/questions/35631903/raw-sql-query-without-dbset-entity-framework-core) – Karan May 14 '20 at 08:45
  • I added those properties and it is working. I'm just wondering is there away around doing that in case I needed to do more/advanced join statements? – Brett Davis May 14 '20 at 08:47
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged – marc_s May 14 '20 at 09:49
  • Also - if you're using EF Core - why on earth are you using such an awful, clunky, messy SQL - can't you just load the entity you're interested in from your EF Core `DbContext` and be done with it?? – marc_s May 14 '20 at 09:50
  • I’m still a student of the entity framework. How can you write the equivalent of a join statement in ET? – Brett Davis May 14 '20 at 09:52

2 Answers2

0

I added the missing properties and it is now working.

Brett Davis
  • 152
  • 1
  • 14
0

you must use FromSqlRaw with using Microsoft.EntityFrameworkCore; instead of FromSql

var staffNotes = _context.StaffNotes.FromSqlRaw("Select s.record_number, s.contact_number, s.To_Staff, s.From_Staff, s.Note_Date, s.Ticket_Number, s.Imp, s.Critical_Date, S.Note, s.write_number, c.first_name, c.last_name from staffnotes s, contacts c where s.contact_number = c.contact_number and s.Contact_Number = " + id).ToList();

alite
  • 1
  • 1