Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved SqlModelMapper dynamically add where clause

  1. How-to
0
Votes
Undo
  1. Aaron D'Hooghe
    Aaron D'Hooghe
  2. SnapObjects
  3. Thursday, 16 November 2023 14:34 PM UTC

Hi,

 

I am using the LoadAsync() methode to load my object from the database.

I was wondering if there is a way to dynamically add a where clause. 

I have class with a SqlParameter called Name. I use this parameter in a where clause. But this parameter can be null. Is there a way to remove te where clause when the parameter is null

 

example:

 

public async Task<IEnumerable<customer>> RetrieveAsync(string name)

{

     var result = await _dataContext.SqlModelMapper.loadAsync<Customer>(name);

    return result.ToList()

} 

 

[SqlParameter("Name", typeof(string))]

[FromTable("Customers")]

[SqlWhere("Name = :Name")]

public class Customer {

        public int Id { get; set; }

        public string Name { get; set; }

}

 

If the parameter Name is null snapobjects will add "where Name = null" to the query, this will return no results ofcourse.

Is there a way to ingore the where when the parameter is null?

 

Kind regards,

SqlModelMapper TrackUpdate not working
LoadByPage
Responses (2)
Francisco Martinez @Appeon
Francisco Martinez @Appeon Accepted Answer Pending Moderation
  1. Thursday, 16 November 2023 15:18 PM UTC
  2. SnapObjects
  3. # 1

You can just check if the variable is null and decide whether to call the function with or without arguments:

 

public async Task<IEnumerable<customer>> RetrieveAsync(string name)
{
     ILoadable<Customer> result;
     if(name == null) {
        result = await _dataContext.SqlModelMapper.LoadAsync<Customer>(name);
     } else {
        await _dataContext.SqlModelMapper.LoadAsync<Customer>();
     }

    return result.ToList()
}

 

Regards,
Francisco

 

 

Edit: The previous code block doesn't compile, please try the following:

public async Task<IEnumerable<Customer>> RetrieveAsync(string name)
{
     ILoadable<Customer> result;
     if(name == null) {
        result = await _dataContext.SqlModelMapper.LoadAll<Customer>();
     } else {
        await _dataContext.SqlModelMapper.LoadAsync<Customer>(name);
     }

    return result.ToList()
}
Comment
Aaron D'Hooghe
  1. Aaron D'Hooghe
  2. Thursday, 16 November 2023 15:44 PM UTC
Hi

Below will give a InvalidOperationException saying "Missing parameter value(s)", so this won't work.



await _dataContext.SqlModelMapper.LoadAsync<Customer>();



King regards
  1. Helpful
Francisco Martinez @Appeon
  1. Francisco Martinez @Appeon
  2. Thursday, 16 November 2023 17:20 PM UTC
Hi Aaron,



I mistakenly believed LoadAsync with no arguments would just load all entries, but it seems there's a separate method for that:

LoadAll(), so please try replacing the functions.



Regards,

Francisco

  1. Helpful
Francisco Martinez @Appeon
  1. Francisco Martinez @Appeon
  2. Thursday, 16 November 2023 17:21 PM UTC
Also, I noticed the condition is backwards, please see my edit
  1. Helpful
There are no comments made yet.
Logan Liu @Appeon
Logan Liu @Appeon Accepted Answer Pending Moderation
  1. Friday, 17 November 2023 02:41 AM UTC
  2. SnapObjects
  3. # 2

Hi Aaron,

Except for the LoadlAll solution as Fransico suggested, I want to add two more solutions:

Solution B): Change the SQL Where condition defined in your C# model. 

[SqlParameter("Name", typeof(string))]

[FromTable("Customers")]

[SqlWhere("Name = :Name or :Name IS NULL")]

public class Customer {

        public int Id { get; set; }

        public string Name { get; set; }

}

 

Solution C): Remove the WHERE clause using GetQueryBuilder.

You can add other where conditions dynamically after GetQueryBuilder.

  var data = await _dataContext.SqlModelMapper.GetQueryBuilder<Department>(removeWhereClause: true)
                .LoadAsync(null, cancellationToken);

Regards, Logan

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.