question

Hi @Brian-7574,

This works but two things I want to change are:

I'd like to use Dapper instead of the long LINQ statement

I'd like to put the SQL Query in my Service layer instead of in my UI layer

Any suggestions for how to do this?

To use Dapper to execute the SQL Query instead of the EF core, you have to install the Dapper Package first. Then, in the service layer you could create a service to execute the sql query statement and return the required properties (CurriculumID and Curriculum), here you need to convert the LINQ query statement to SQL Query statement. In the UI layer, you can call the relate service and get the required properties and then based on the result to create a list of SelectListItem. Finally render the data in the view page.

Here is sample you could refer it:

1. Based on the query result create a CurriculumViewModel

     public class CurriculumViewModel
        {
            public int CurriculumID { get; set; }
            public string Curriculum { get; set; }
        }
   

   
   

2. Create a CurriculumService with the following code:

     public interface ICurriculumService
        {
            Task<IEnumerable<CurriculumViewModel>> GetCurriculumAsync();
        }
   
        public class CurriculumService : ICurriculumService
        {
            private readonly SqlServerConnectionProvider _provider;
            public CurriculumService(SqlServerConnectionProvider provider)
            {
                _provider = provider;
            }
            public async Task<IEnumerable<CurriculumViewModel>> GetCurriculumAsync()
            {
                using (var connection = _provider.GetDbConnection())
                {
                    var querystr = "SELECT cur.CurriculumID, cur.Curriculum FROM Curriculum as cur " +
                           " LEFT JOIN Courses as c ON cur.CurriculumID = c.CurriculumID " +
                           "Where cur.CurriculumStatusID = 1 and c.OnDemand != '0' order by cur.Curriculum";
                   
                    return await connection.QueryAsync<CurriculumViewModel>(querystr, null);
                }
            }
        }
   

   The SqlServerConnectionProvider class as below:

     public class SqlServerConnectionProvider
        {
            private readonly IConfiguration _config;
            private string Connectionstring = "DefaultConnection";
            public SqlServerConnectionProvider(IConfiguration configuration)
            {
                _config = configuration;
            }
   
            public IDbConnection GetDbConnection()
            {
                return new SqlConnection(_config.GetConnectionString(Connectionstring));
            }
        }
   

   
   

3. Register the service in the ConfigureServices method:

             //Register dapper in scope    
                services.AddScoped<SqlServerConnectionProvider>();
                services.AddScoped<ICurriculumService, CurriculumService>();
   

   
   

4. Call the service in the Index Razor page:

   Index.cshtml.cs:

     public class IndexModel : PageModel
        {
            private readonly ILogger<IndexModel> _logger;
            private readonly ICurriculumService _repository;
            public IndexModel(ILogger<IndexModel> logger, ICurriculumService datatRepository)
            {
                _logger = logger;
                _repository = datatRepository;
            }
            [BindProperty]
            public List<SelectListItem> Curricula { get; set; }
   
            [BindProperty]
            public string Curriculum { get; set; }
            public void OnGet()
            {
                Curricula = _repository.GetCurriculumAsync().Result.ToList().Select(c => new SelectListItem { Value = c.CurriculumID.ToString(), Text = c.Curriculum }).ToList();
            }
        }
   

   
   

   Index.cshtml:

     @page
        @model IndexModel 
   
        <select asp-for="Curriculum" class="form-control" asp-items="Model.Curricula"></select>
   

   
   

   The screeshort as below:

   

   ---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion