C#•14mo ago

✅ T-SQL Statement in Linq

Hey, I'm trying to reduce the amount of stored procedures and write that stuff in C# / Linq. Right now I'm trying to rewrite this: if @shouldValidate = 1
begin
while @RowCount >= @ViewId
begin
begin try
select @ViewName = ViewName from #ViewData where #ViewData.ViewId = @ViewId
set @SqlQuery = N'set noexec on; SELECT TOP 1 * FROM [' + @ViewName + ']; set noexec off;';

exec sp_executesql @SqlQuery;

update #ViewData
SET ViewStatus = 1,
ViewMessage = 'No Error'
where #ViewData.ViewId = @ViewId;
end try
begin catch
update #ViewData
SET ViewStatus = 2,
ViewMessage = ERROR_MESSAGE()
where #ViewData.ViewId = @ViewId;
end catch

set @ViewId = @ViewId + 1
end;
end; background: from time to time an external software partner changes his release and some views from my colleagues run on an error then, because some tables got changed and columns are missing. only twist there is the "set noexec on" to avoid resultsets and being able to loop that stuff without having x results for x views. an example result is added as image. Can anyone help me to write that in Linq? I'm new to that and tried this so far, but doesnt work: public async Task<ViewEntity[]> ValidateViews(string searchText) { viewData = await GetViews(searchText); foreach (ViewEntity data in viewData) { try { var result = await _database.ViewEntities.FromSql($"set noexec on; SELECT TOP 1 * FROM [{data.ViewName}]; set noexec off;").ToListAsync(); data.ViewStatus = 1; data.ViewMessage = "No Error"; } catch (Exception ex) { data.ViewStatus = 2; data.ViewMessage = ex.Message; } } return viewData.ToArray(); } having a problem with viewEntities there, because it doesnt fit to the result..and probably more, but you can tell me that xD Thanks for any advice in the right direction! :) Best regards Exi

43 Replies

ExiOP•14mo ago

I found a working solution for now, but still unhappy: public async Task<ViewEntity[]> ValidateViews(string searchText) { viewData = await GetViews(searchText); foreach (ViewEntity data in viewData) { try { var result = await _database.Database.ExecuteSqlRawAsync($"set noexec on; SELECT TOP 1 * FROM [{data.ViewName}]; set noexec off;"); data.ViewStatus = 1; data.ViewMessage = "No Error"; } catch (Exception ex) { data.ViewStatus = 2; data.ViewMessage = ex.Message; } } return viewData.ToArray(); } My problem: It's kinda slow 1st run on the server as sp => like 8 seconds (for 260 views~) other runs after optimization and executionplan < 1 second if I run this method it always runs like 8 seconds and I assume it has no optimized plan and each call is like I had a recompile at my stored procedure, right? any way to increase the performance?

JakenVeina•14mo ago

$code

MODiX•14mo ago

To post C# code type the following: ```cs // code here ``` Get an example by typing $codegif in chat For longer snippets, use: https://paste.mod.gg/

JakenVeina•14mo ago

if @shouldValidate = 1 begin
    while @RowCount >= @ViewId begin
        begin try
            select @ViewName = ViewName
            from #ViewData
            where #ViewData.ViewId = @ViewId
            set @SqlQuery = N'
                set noexec on;

                SELECT TOP 1 *
                FROM [' +  @ViewName + '];

                set noexec off;';

            exec sp_executesql @SqlQuery;

            update #ViewData
            SET ViewStatus = 1,
                ViewMessage = 'No Error'
            where #ViewData.ViewId = @ViewId;
        end try
        begin catch
            update #ViewData
            SET ViewStatus = 2,
                ViewMessage = ERROR_MESSAGE()
            where #ViewData.ViewId = @ViewId;
        end catch

        set @ViewId = @ViewId + 1
    end;
end;

if @shouldValidate = 1 begin
    while @RowCount >= @ViewId begin
        begin try
            select @ViewName = ViewName
            from #ViewData
            where #ViewData.ViewId = @ViewId
            set @SqlQuery = N'
                set noexec on;

                SELECT TOP 1 *
                FROM [' +  @ViewName + '];

                set noexec off;';

            exec sp_executesql @SqlQuery;

            update #ViewData
            SET ViewStatus = 1,
                ViewMessage = 'No Error'
            where #ViewData.ViewId = @ViewId;
        end try
        begin catch
            update #ViewData
            SET ViewStatus = 2,
                ViewMessage = ERROR_MESSAGE()
            where #ViewData.ViewId = @ViewId;
        end catch

        set @ViewId = @ViewId + 1
    end;
end;

so, this query updates a table #ViewData to flag certain views when they have been changed by a third party, and are thus no longer compatible with your code or, rather it flags views that are broken, due to changes in underlying tables, by a third party yes?

ExiOP•14mo ago

Yea, #ViewData is just a temp table with collected views filtered by name, in case you dont want to validate all of them. And as you said the query basically just runs a select top 1 for each view and updates the #ViewData based on the sql response. (You either get an error or just the notification, that the query completed succesfully). Because of that I handle it via try and catch

JakenVeina•14mo ago

so A) there's almost certainly a way to query for broken objects in the system that would ostensibly eliminate this entire thing

SELECT *
FROM SYS.ALL_VIEWS V
WHERE V.VIEW_NAME MEMBER OF @viewNames
    AND V.OBJECT_STATUS = 'INVALID'

SELECT *
FROM SYS.ALL_VIEWS V
WHERE V.VIEW_NAME MEMBER OF @viewNames
    AND V.OBJECT_STATUS = 'INVALID'

something like that so, one query you run, that you can pretty-easily map into EF Core that aside B) the procedure you HAVE is an excellent example of something that doesn't belong in EF Core anyway it involves back-and-forth DML with the database, and doing that via queries built by and mapped in EF Core will involve multiple round trips between the database and application layer that's pretty much unavoidable in other words you have a unit of work you want to perform that involves temporary/intermediate results results that you don't return, you just use them to make decisions EF Core doesn't support this scenario, except to split them into multiple operations so, you run a query wait a round-trip for results process the results in app and write a followup update operation to perform at MINIMUM, that's 2 round-trips, versus 1 if you put the operation in a stored procedure

ExiOP•14mo ago

    public async Task<ViewEntity[]> GetViews(string searchText)
    {
        var result = await Task.FromResult(
            _database.ViewEntities.FromSql($"SELECT ViewName = TABLE_NAME, ViewScheme = TABLE_SCHEMA, ViewDatabase = TABLE_CATALOG, ViewStatus = 0, ViewMessage = 'Not validated' FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE ('%' + {searchText} + '%')")
        );
        return result.ToArray();
    }

    public async Task<ViewEntity[]> ValidateViews(string searchText)
    {
        viewData = await GetViews(searchText);

        foreach (ViewEntity data in viewData)
        {
            try
            {
                var result = await _database.Database.ExecuteSqlRawAsync($"set noexec on; SELECT TOP 1 * FROM [{data.ViewName}]; set noexec off;");
                data.ViewStatus = 1;
                data.ViewMessage = "No Error";
            }
            catch (Exception ex)
            {
                data.ViewStatus = 2;
                data.ViewMessage = ex.Message;
            }
        }
        return viewData.ToArray();
    }

    public async Task<ViewEntity[]> GetViews(string searchText)
    {
        var result = await Task.FromResult(
            _database.ViewEntities.FromSql($"SELECT ViewName = TABLE_NAME, ViewScheme = TABLE_SCHEMA, ViewDatabase = TABLE_CATALOG, ViewStatus = 0, ViewMessage = 'Not validated' FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE ('%' + {searchText} + '%')")
        );
        return result.ToArray();
    }

    public async Task<ViewEntity[]> ValidateViews(string searchText)
    {
        viewData = await GetViews(searchText);

        foreach (ViewEntity data in viewData)
        {
            try
            {
                var result = await _database.Database.ExecuteSqlRawAsync($"set noexec on; SELECT TOP 1 * FROM [{data.ViewName}]; set noexec off;");
                data.ViewStatus = 1;
                data.ViewMessage = "No Error";
            }
            catch (Exception ex)
            {
                data.ViewStatus = 2;
                data.ViewMessage = ex.Message;
            }
        }
        return viewData.ToArray();
    }

JakenVeina•14mo ago

yeah

ExiOP•14mo ago

I did it like this now, basically just using the querys as in the procedure

JakenVeina•14mo ago

and here, you have N round-trips even worse and unfortunately, I'm wrong

Unlike Oracle, SQL Server does not have any meta-data table which can be used to find invalid/broken database objects.

that really surprises me so, stick with the stored procedure you have that's the most-effective way to do this whether you update some table that tracks invalid objects, or you just return the list of invalid ones to the caller

ExiOP•14mo ago

the problem with the stored procedure is, that they have a lot of databases and update them to different versions sometimes and my custom stored procedure doesnt get carried to all versions. the plan was to get that view check into an web interface to make it easier to validate quickly without the need to access the database manually. if the procedure doesnt exists that just runs on an error and I was trying to do that inside my code and query directly to resolve that problem

JakenVeina•14mo ago

why doesn't it get carried to all versions? also, how often would you update that stored procedure?

ExiOP•14mo ago

because their system sucks

JakenVeina•14mo ago

why would it need to be updated?

ExiOP•14mo ago

it doesnt need to be updated, but it needs to be there :D

JakenVeina•14mo ago

I mean so, is this not your database at all? like the views you're querying from aren't yours?

ExiOP•14mo ago

sadly the server / databases gets managed by an external provider views are from an external softwarepartner + colleagues

JakenVeina•14mo ago

okay, so they're not yours welp

ExiOP•14mo ago

there are a few rocks in the way xd

JakenVeina•14mo ago

I think personally I'd just go with "catch exceptions and interpret them" if you're querying from... whatever and an exception comes back cause some object is broken you ought to be able to detect that in the exception via error code or message or whatever if you have some way to recover from that, then you can do that you said you want to check which views are valid, so you can write your own query that bypasses them? honestly, sounds like you should just do that, period or go the slightly-malicious route and error out completely "Services from XXX (third party here) are currently unavailable" make people start asking "why the hell is XXX down all the time?"

ExiOP•14mo ago

wanna check if valid and otherwise get the information to the correct person get that fixed before one dude did this by hand, opened the view, queried it, wrote that result down in a spreadsheet that took ages just wanted to give him a hand

JakenVeina•14mo ago

sure whatever the case, I think the appropriate thing is to interpret exceptions on the fly now if your intention was to run this validation routine in the background, periodically.... then it's probably effective the multiple round-trips aren't that big of a deal, then

ExiOP•14mo ago

for now just if needed

JakenVeina•14mo ago

like, upon request?

ExiOP•14mo ago

u remember that multi tenancy topic from earlier? i implemented that now and whoever can switch tenants in an webinterface and make a view check there

JakenVeina•14mo ago

like, when this guy goes to click a button to retrieve the list of bad views?

ExiOP•14mo ago

yea

JakenVeina•14mo ago

if you can't deploy a stored procedure for it, then the .NET implementation you posted is the way to go UNLESS there's some way you can write all those view checks into one query

ExiOP•14mo ago

oh well, that's something I could try

JakenVeina•14mo ago

I'm not sure how unless it's just....

set noexec on;

SELECT TOP 1 * FROM [@viewName0];
SELECT TOP 1 * FROM [@viewName1];
SELECT TOP 1 * FROM [@viewName2];
SELECT TOP 1 * FROM [@viewName3];

set noexec off;

set noexec on;

SELECT TOP 1 * FROM [@viewName0];
SELECT TOP 1 * FROM [@viewName1];
SELECT TOP 1 * FROM [@viewName2];
SELECT TOP 1 * FROM [@viewName3];

set noexec off;

I dunno if SQL Server supports retrieving results like that

ExiOP•14mo ago

but how would I detected the wrong ones then and update it?

JakenVeina•14mo ago

you'd.... inspect the results of each of those queries? to see if an exception occurred or not? and update what? this is the version that isn't updating anything

ExiOP•14mo ago

mh maybe I dont get your idea, but if I run a query like that, it just stops at the first error and gives me the response for that

JakenVeina•14mo ago

then you can't do it that way and I fall back to

I'm not sure how

in response to

UNLESS there's some way you can write all those view checks into one query

ExiOP•14mo ago

thats the prototype how it should work in theory. I guess just the decision has to be made: A. Stick with procedure to increase performance B. Query it in code to stay flexible (no matter what db to query)

JakenVeina•14mo ago

8 seconds is unfortunate, but for something that's being run in-frequently and is essentially an admin-tool, not user-facing, I don't see it as a big deal

ExiOP•14mo ago

ye and 115 like on the screenshot take like 2-3 seconds ~ I assume the guy who did that by hand before will still be happy :D

JakenVeina•14mo ago

if you REALLY want, you could do both catch the error if the procedure is missing, and do it by hand instead

ExiOP•14mo ago

I also thought about storing the procedure somewhere and if she doesnt exist, i create it but because some servers/databases arent managed by us, maybe the permissions arent there and i left that idea :/

JakenVeina•14mo ago

if you have perms to do that on the fly, yeah, I'd say go for it yeah realistically, deploying objects is not something you should be allowed to do, outside of a deployment window

ExiOP•14mo ago

okay I think that's fine for now. Quick question before I close this: i've a blazorwasm app and wanna save some filters (for example checkboxes) to still have them after refresh. Local Storage for that ?

JakenVeina•14mo ago

prolly unless you wanna send 'em to the database

ExiOP•14mo ago

nah just simple for each user

Gaming

Programming

✅ T-SQL Statement in Linq