I am trying to use raw SQL in .NET Core instead of normal EF Core approach.
Here is my query and what I am trying to do with it.
Entities used here are in a one-to-many relationship as one project can have multiple tasks:
public class Project : IId
{
public int Id { get; set; }
public string Reference { get; set; }
public DateTime CreatedOn { get; set; }
public List<Task> Tasks { get; set; }
public string ACode { get; set; }
public string ICode { get; set; }
public string Scheme { get; set; }
public int? Ticket { get; set; }
public DateTime Inception { get; set; }
private StatusCalculator StatusCalculator { get; set; }
}
public class Task : IId
{
public int Id { get; set; }
public int ProjectId { get; set; }
[JsonIgnore] public Project Project { get; set; }
public Type Type { get; set; }
public Status Status { get; private set; }
public string CompletedBy { get; private set; }
public DateTime? CompletedOn { get; private set; }
public DateTime? Deadline { get; set; }
public string CreatedBy { get; private set; }
public DateTime? CreatedOn { get; private set; }
public int? Ticket { get; set; }
public string Comment { get; set; }
}
I am trying following approach
public Project GetProject(string surname, string phone, string email, int page, int limit)
{
var project = DbContext.Database.ExecuteSqlRaw(string.Format($"SELECT P.ProjectId,P.Reference,P.CreatedOn,P.ACode,P.ICode,P.Scheme,P.Ticket,P.Inception,P.Status," +
"T.TaskId,T.ProjectId,T.Type,T.Status,T.CompletedBy,T.CompletedOn,T.Deadline,T.CreatedBy,T.CreatedOn,T.Ticket FROM TASK T " +
"INNER JOIN Project P ON P.ProjectId = T.ProjectId " +
"INNER JOIN CS26 C26 ON LEFT(C26.Bran,1) = LEFT(P.Branch,1) AND C26.Client = P.Client AND C26.Ref = P.Ref " +
"INNER JOIN A_C AC ON LEFT(AC.Bran,1) = LEFT(C26.Bran,1) AND AC.Client = C26.Client " +
"where AC.Surname = {0} and AC.Phone = {1} and C26.WEBADDR = {2} " +
"order by P.CreatedOn desc",surname,phone,email));
return project;
}
but it seems that ExecuteSqlRaw returns only an int - I need a way so I can use raw SQL and map the result returned to entities as shown above, so that expected result set can be obtained.
I need to fetch
Project -> Tasks array (existing tasks)
After checking the official documents, you will find
context.Blogs
.FromSql($"SELECT * FROM dbo.Blogs")
.ToList();
It is not suitable for complex multi-table query statements. ExecuteSqlRaw returns an instance of the int type, which determines whether the execution is complete.
If you still don't want, you can check the below content.
If you don't want to use a third-party ORM framework, then you can consider using DataTable to receive the return results and convert them into the Entities you want.
Here is the sample I wrote before.
Github: ISqlHelperService
And here is the method to convert DataTable to List<T>. Like below:
public static List<T> ToListByDataTable<T>(DataTable dtTemp)
{
if (dtTemp == null || dtTemp.Rows.Count == 0)
{
return null;
}
List<T> lstResult = new List<T>();
for (int j = 0, l = dtTemp.Rows.Count; j < l; j++)
{
T _t = (T)Activator.CreateInstance(typeof(T));
PropertyInfo[] propertys = _t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
for (int i = 0, k = dtTemp.Columns.Count; i < k; i++)
{
if (pi.Name.ToLower().Equals(dtTemp.Columns[i].ColumnName.ToLower()))
{
if (dtTemp.Rows[j][i] != DBNull.Value)
{
switch (pi.PropertyType.ToString())
{
case "System.Int32":
pi.SetValue(_t, int.Parse(dtTemp.Rows[j][i].ToString()), null);
break;
case "System.Int64":
pi.SetValue(_t, long.Parse(dtTemp.Rows[j][i].ToString()), null);
break;
case "System.DateTime":
pi.SetValue(_t, DateTime.Parse(dtTemp.Rows[j][i].ToString()), null);
break;
case "System.String":
pi.SetValue(_t, dtTemp.Rows[j][i].ToString(), null);
break;
case "System.Boolean":
pi.SetValue(_t, Boolean.Parse(dtTemp.Rows[j][i].ToString()), null);
break;
case "System.Guid":
pi.SetValue(_t, Guid.Parse(dtTemp.Rows[j][i].ToString()), null);
break;
case "System.Single":
pi.SetValue(_t, Convert.ToSingle(dtTemp.Rows[j][i].ToString()), null);
break;
case "System.Double":
pi.SetValue(_t, Convert.ToDouble(dtTemp.Rows[j][i].ToString()), null);
break;
case "System.Object":
pi.SetValue(_t, dtTemp.Rows[j][i], null);
break;
}
}
else
{
switch (pi.PropertyType.ToString())
{
case "System.Int32":
pi.SetValue(_t, -1, null);
break;
case "System.Int64":
pi.SetValue(_t, -1, null);
break;
case "System.DateTime":
pi.SetValue(_t, new DateTime(0x76c, 1, 1), null);
break;
case "System.Boolean":
pi.SetValue(_t, false, null);
break;
case "System.Guid":
pi.SetValue(_t, Guid.Empty, null);
break;
case "System.Single":
pi.SetValue(_t, 0.0f, null);
break;
case "System.Double":
pi.SetValue(_t, 0.0, null);
break;
case "System.String":
pi.SetValue(_t, string.Empty, null);
break;
default:
pi.SetValue(_t, null, null);
break;
}
}
break;
}
}
}
lstResult.Add(_t);
}
return lstResult;
}