How to write sql code with fastcrud?

Question

classes:

[Table("department_types", Schema = "public")]
public class DepartmentTypes


[Table("departments", Schema = "public")]
    public class Department

query:

private string BuildQuery()
            {
                return OrmConfiguration.GetSqlBuilder<DepartmentViewModel>().Format(
                    $@" SELECT {nameof(Department.Id):C},
                                {nameof(Department.Name):C},
                                {nameof(Department.TypeId):C},
                                {nameof(DepartmentTypes.Name):C} as DepartmentTypeName
                        FROM {nameof(Department):T}
                        LEFT JOIN {nameof(DepartmentTypes):T}
                        ON {nameof(Department.TypeId):C} = {nameof(DepartmentTypes.Id):C}");
            }

result class:

public class DepartmentViewModel
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public int TypeId { get; set; }

        public string DepartmentTypeName { get; set; }

    }

I want to make a join with Department and DepartmentTypes and get a DepartmentViewModel result...

Now instead of {nameof(DepartmentTypes):T} (for example) system puts "DepartmentViewModel", not the name of DepartmentTypes which is: "department_types"

score 1 · Accepted Answer · answered Feb 24 '17 at 13:55

I found the answer :D.

private string BuildDepartmentListQuery()
            {
                var departmentTypeTable = Sql.Table<DepartmentType>();
                var departmentTable = Sql.Table<Department>();

                var departmentColumnId = Sql.TableAndColumn<Department>(nameof(Department.Id));
                var departmentColumnName = Sql.TableAndColumn<Department>(nameof(Department.Name));
                var departmentColumnTypeId = Sql.TableAndColumn<Department>(nameof(Department.TypeId));
                var departmentColumnManagerId = Sql.TableAndColumn<Department>(nameof(Department.ManagerId));

                var departmentTypeColumnId = Sql.TableAndColumn<DepartmentType>(nameof(DepartmentType.Id));
                var departmentTypeColumnName = Sql.TableAndColumn<DepartmentType>(nameof(DepartmentType.Name));

                string sqlBuilderResult = OrmConfiguration.GetSqlBuilder<Department>()
                    .Format(
                        $@"SELECT
      {departmentColumnId}
    , {departmentColumnName}
    , {departmentColumnTypeId}
    , {departmentColumnManagerId}
    , {departmentTypeColumnName} as ""DepartmentTypeName""
    FROM {departmentTable}
    LEFT JOIN {departmentTypeTable}
    ON {departmentColumnTypeId} = {departmentTypeColumnId}");
                ;

                return sqlBuilderResult;
    }

If it would be easier, I tried it your way and then just did it where I created my own string interpolated raw SQL query. As long as you have proper table identifiers tied to your columns, it will still work. In addition, since you are declaring as your entity, you can use the :T and :C items to shortcut some of your table/column declarations. — Rob W., Mar 24 '17 at 19:24

How to write sql code with fastcrud?

1 Answers1