Stored Procedure Potential Mapping Issue - Returning Empty Array in service

I recently integrated SS into an MVC project that calls a stored procedure to return some search results. I will post the code below. Recently it started happening that some search terms were returning empty results however if I execute the exact SQL:

 exec GetEntitiesInformation 'term', ''

I get results.

I have outputted the sql statement and it is correct and runs fine in SQL Studio. My only guess is it is some kind of mapping issue as the stored procedure is returning some data from a view that does a bunch of unions. Would this cause an issue and if so how would I go about troubleshooting it and/or do you have any advice?

Query with results:

Webservice returning no results:

The code isn’t complex or anything and all fields are strings except the type which always is an int as it is hard coded in the view:

    public class EntityInformation
        public string EntityID { get; set; }
        public string ExternalID { get; set; }
        public string Name { get; set; }
        public Enums.EntityType EntityType { get; set; }
        public string ClientID { get; set; }

public class SearchLogic : BaseLogic, ISearchLogic
        public IRepository<EntityInformation> EntitiesInformation { get; set; }
        public List<EntityInformation> GetEntitiesByTextFilter(string textFilter)
            var data = EntitiesInformation.Exec("GetEntitiesInformation", textFilter,  "");
            return data;

 public class CommonService : BaseService
        public ISearchLogic SearchLogic { get; set; }

        public List<EntityInformation> Get(SearchByTextFilter request)
            return SearchLogic.GetEntitiesByTextFilter(request.TextFilter);

Exec on the Repository:

 public List<T> Exec(string spName, params object[] args)
            var sqlExpression = $"EXEC { spName } " +
                string.Join(",", args.Select(x => x is string ? $"'{ x.ToString() }'" : x.ToString()));            
            using (var db = DbFactory.OpenDbConnection())
                return db.SqlList<T>(sqlExpression);

You can try setting a logger to see if that sheds any lights, e.g:

LogManager.LogFactory = new ConsoleLogFactory(debugEnabled:true);

You can also try selecting a dynamic result set like List<object> or Dictionary<string,object> to see if the results are populated.

I will try that when we can update production, however if there is some mapping issue with the columns could that account for returning an empty result? I noticed there are several search results that never return and maybe their columns are being treated as ints or something.

Recycling IIS fixed the issue. I have no idea why.

1 Like