Mock results of SP?

[MarkLFT]

I have a number of Stored Procedures that take arguments and return a list of objects. With the actual database it works well. I am however trying to mock the results of the SP so can be used in integration testing.

I can mock table contents, but cannot find a way to mock the results of a stored procedure. Can you please advise how to do this.

Many thanks

[sjh37]

Hi @MarkLFT Great question! I don't really have anything with regard to calling stored procs. All it does is return a new List<T>

public DbSet<TenMostExpensiveProductsReturnModel> TenMostExpensiveProductsReturnModel { get; set; }
public List<TenMostExpensiveProductsReturnModel> TenMostExpensiveProducts()
{
    int procResult;
    return TenMostExpensiveProducts(out procResult);
}

public List<TenMostExpensiveProductsReturnModel> TenMostExpensiveProducts(out int procResult)
{
    procResult = 0;
    return new List<TenMostExpensiveProductsReturnModel>();
}

public Task<List<TenMostExpensiveProductsReturnModel>> TenMostExpensiveProductsAsync()
{
    int procResult;
    return Task.FromResult(TenMostExpensiveProducts(out procResult));
}

Thinking about it, the returns should be:

return TenMostExpensiveProductsReturnModel.ToList();

That way, you can fill in the stored procs DbSet<TModel> with any data you like and have that be returned by the proc. If you want to go ahead with testing that, change

return new {{ReturnType}}();{{#newline}}

with

return {{ReturnModelName}}.ToList();{{#newline}}

[sjh37]

Actually, you can't replace all return new {{ReturnType}}();{{#newline}} as some just simply return a model, and not a List. You'll have to check the template for the one that returns the List. And for the one that simple returns a new TModel, perhaps we could set it to return the First() of the DbSet<TModel> ?

Let me know your thoughts and have a play to see what suits your needs best and let me know. I can make those changes to make them perment in the generator so you don't have to tweak anything for future releases.

[MarkLFT]

Sounds good. Will have a play.

[MarkLFT]

Have had a play and got it working after a fashion.

Because I am using DI, and the interface does not contain the spResults DbSet, I was forced to use the following code to access the Dbset to populate it.

`var db = _provider.GetRequiredService<IGDSDbContext>();

FakeDbSet<SpGetRatesReturnModel>? spResults = (FakeDbSet<SpGetRatesReturnModel>?)(typeof(FakeGDSDbContext).GetProperty("SpGetRatesReturnModels")?.GetValue(db));`

Once I had this, I could populate the FakeDbSet with some fake results. A problem I had, was I needed to pre-filter what the results were based on the parameters that I would normally pass to the SP. This meant I had to create a number of tests with different scenarios, which was a bit of a pain.

A work around for this, was to manually adjust the fake sp call method, to filter the list based on properties, but as the reverse engineering of the sp cannot know how the filters work, I must enter this manually. But doing this allows me to load the sp DBSet with all possible results and filter them as the program usually would.

You point about an sp that returns a single value is understood, however all of mine return lists, so have not really had a chance to look at this.

I think if the FakeDbCOntext is instantiated the way you do, you would have less issues. But for now, I have my sp's working as I need them, just with the pain I need to update the filter code in the SP's each time I save the tt file.

[sjh37]

Thanks Mark. Can you send me a small example of the code. Not sure what I can do about the filter code, other than tweaking the template, or having an Action/Func to call.. once I see your code I will have better understanding. DI for a unit test is always difficult as it's the fake concrete type that has the sp DbSet and not the interface.