2

I'm trying to get all records from SQL database using DapperExtensions.

But I have a Schema set to other than dbo for some tables. Hence, the table is not recognized from sql query.

For example, a table is in the form [Schema][TableName]. But when I start query, error is thrown like:

Invalid object name 'TableName'.

This is the Model class:

using System;
using Dapper.Contrib.Extensions;
using ImOnTech.Teftis.Core.Models;
using ImOnTech.Teftis.Core.Models.DT_Inspection;

namespace ImOnTech.Teftis.Core.Models.DT_Inspection
{
    [Table("DT_Inspection.City")]

    public class City
    {

This is the function to GetAll records from database:

public async Task<IReadOnlyList<City>> GetAllAsync()
        {
            var CityList = await Context.Connection.GetListAsync<City>();
            Context.Connection.Close();
            return CityList.ToList();
          
        }
Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
veli
  • 249
  • 1
  • 5
  • 14

1 Answers1

2

While mapping your models, be bit more explicit. Mention the Schema explicitly.
Following is an example how to provide various mapping properties.

public class Customer
{
    public int CustomerID { get; set; }
    public string Name { get; set; }
}

public sealed class CustomerMapper : ClassMapper<Customer>
{
    public CustomerMapper()
    {
        Schema("dbo");
        Table("Customer");
        Map(x => x.CustomerID).Key(KeyType.Identity);
        AutoMap();
    }
}

Please note that, if your column names and property name in model is same, you do not need to call Map for each property (the way I did above Map(x => x.CustomerID).Key(KeyType.Identity);). Instead, only call AutoMap(); and properties will be automatically mapped.

To make these mappings known to Dapper Extensions, call the following code only once at application startup:

DapperExtensions.DapperExtensions.SetMappingAssemblies(new[] { Assembly.GetExecutingAssembly() });
Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
  • Hi, do i need to define a separate Mapper class for every model class i have? I have lots of such Models in a separate project. Is there any easy/better way to mark these models/classes with their Schema() decorated? I really could not find out any material or documentation regarding that. Can you please help in this regard? – Ak777 Jan 04 '23 at 22:00
  • @Ak777: Newer version of Dapper Extensions is now available. I am not sure the feature you are looking for is available now; I am not using this ORM anymore. Anyway, that is not a huge code anyway to map each model. The `AutoMap()` makes it easy. – Amit Joshi Jan 05 '23 at 07:47