1. Daryl Foster
  2. SnapObjects
  3. Thursday, 28 May 2020 08:02 AM

Hi,

I'm having an issue with SqlModelMapper when I'm trying to Load with a modified SqlQueryBuilder.  If I call SqlExecutor.Select with the SqlQueryBuilder it works ok, but if I call SqlModelMapper.Load with the same SqlQueryBuilder, it seems to ignore it and just use it's original query.

I want to use SqlModelMapper to take advantage of the nested model feature, otherwise I would just use SqlExecutor.

Originally I noticed the issue with some complex sql and model, but I can reproduce it with a simple model and sql.  My function is below (I just uncomment the relevant return line to test both cases). Can anyone see any issues with my code?

        public IList<codeDetails> Retrieve()
        {
            ISqlQueryBuilder queryBuilder =
                ModelSqlBuilder.GetBuilder<codeDetails>(_dataContext).QueryBuilder;

            queryBuilder.AsWhere().AndWhereRaw("code_details.table_id = 'PN'");

            string sql = queryBuilder.ToSqlString(_dataContext);
            Console.WriteLine(sql);

            // This line works as expected
            //return _dataContext.SqlExecutor.Select<codeDetails>(queryBuilder).ToList();

            // This line ignores the modified queryBuilder
            return _dataContext.SqlModelMapper.Load<codeDetails>(queryBuilder).ToList();
        }

 

Govinda Lopez @Appeon Accepted Answer Pending Moderation
  1. Friday, 29 May 2020 02:38 AM
  2. SnapObjects
  3. # 1
0
Votes
Undo

Hi Daryl, 

 

Can you open a support ticket with a very simple reproducible test case? That would help us pinpoint out the problem.

 

 

Regards,

Comment
Thanks Govinda, I've created a simple console app using the AdventureWorks2012 database that reproduces the problem, so I'll open a support ticket with that.
  1. Daryl Foster
  2. Friday, 29 May 2020 04:04 AM
There are no comments made yet.
Govinda Lopez @Appeon Accepted Answer Pending Moderation
  1. Thursday, 28 May 2020 15:23 PM
  2. SnapObjects
  3. # 2
0
Votes
Undo

Hi Daryl,

 

You can accomplish this in different ways. I think that the one that's closest to your approach is this: https://docs.appeon.com/appeon_online_help/snapobjects2.0/api_reference/SnapObjects.Data/SqlModelMapper/ISqlModelMapper/Method/Load2.html If you look into the "Example" you will see this clearly illustrated. 

 

I hope this helps.

 

Regards,

Comment
Thanks Govinda, that is the example I used to write my code. In the example it uses queryBuilder.Where to add a where clause, whereas I use queryBuilder.AsWhere().AndWhereRaw to append to an existing where clause. To test it out I removed the existing where clause from the model and used queryBuilder.Where but the SqlModelMapper.Load still seems to ignore the modified queryBuilder.



In the example it uses .FirstOrDefault(), but I need to use .ToList() to return multiple records.



My main aim is to be able to



1. Dynamically modify the where clause of a model which has nested models

2. Retrieve a list of the model with nested models



That is why I'm trying to use the SqlModelMapper. (My sample code above is just a simple case without a nested model so I haven't used .IncludeAll() after the Load).



Is there another way to retrieve nested models?
  1. Daryl Foster
  2. Friday, 29 May 2020 01:20 AM
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.