How to write Complex SQL but use Predicate (through Dapper)

Question

Am using Dapper library with Sql Server. Is it possible to use Predicates with Complex Sql Queries to pass the required parameters in order to filter the Sql Resultset out into a Single Entity Class?

SELECT * FROM Config.Country 
INNER JOIN Config.State 
ON Config.State.CountryId = Config.Country.CountryId

The entity class could have the following structure.

public sealed class CountryStateReadDto
{
    //Country table
    public byte CountryId { get; set; }
    public string CountryName { get; set; }
    public string CountryCode { get; set; }
    public string ISOCode { get; set; }
    public string DailingCode { get; set; }
    public string WebCode { get; set; }
    public double Longitude { get; set; }
    public double Latitude { get; set; }

    //State table
    public short StateId { get; set; }
    public string StateName { get; set; }
}

I know, there is a beautiful Dapper-Extension project that does the job, but just for Single Table only.

I need to write Complex Sql Queries preferably using Predicates. Can someone please help me by providing some hints or even solutions would be very helpful too!

If Predicate is not an ideal solution then is there anything else I can consider or better than Predicate?

Note: The above Sql example is just a simple query, I have complex queries with > 10 table joins.

Answer 1

I would suggest creating a view

Create View StateDetails
SELECT * FROM Config.Country 
INNER JOIN Config.State 
ON Config.State.CountryId = Config.Country.CountryId

Once you have your views setup, working with Dapper becomes much easier.