Getting wrong data from SQL view when putting it into a C# list

user_17673770 0
Viewed 107

I have a SQL view that I want to use and put it into a C# list into an ASP.NET MVC project so that I can execute a query on that list. The problem is that I get wrong data from the view.

When I try to get all the data from the SQL view, the count is correct but some rows are shown multiple times and some are simply gone. Same thing happens when I try go execute some logic on the data from that view.

[HttpGet]
[Route("Home/Testing/{godina}/{istorija}/{decursus_id}/{therapy_id}")]
public ActionResult Testing(string godina, string istorija, int decursus_id, string therapy_id)
{
    List<v_as_stac_terapija_komercijalni> terapijaKomercijalniList = db.v_as_stac_terapija_komercijalni
        .Where(x => x.godina == godina
                    && x.istorija == istorija
                    && x.decursus_id == decursus_id
                    && x.therapy_id == therapy_id)
        .ToList();

    return View();
}

So according to the data in my SQL view and according to the parameters given in this action, the list should hold 3 results, and the count is 3 but the problem is I always get the first record that matches this parameters 3 times, instead of getting the real 3 records.

I can provide additional information if necessary. Thanks

asp.net-mvc sql-server linq c#
1 Answers

You've not really told us much about what RDBMS you're using, nor do we know what the view looks like and what underlying tables it might query to get its result set.

From my own personal experience, I know using data from views in EF can be a bit tricky. Possibly, the trouble just simply is the lack of an explicit primary key. Every table in your database ought to have a proper, well defined primary key.

What happens here is: since there's no primary key, EF will just use all non-nullable columns from the view as a "replacement" PK.

And when EF reads the data, it will go:

  • read the row
  • check the primary key (or the "stand-in" PK in this case)
  • if it has already read a row with that PK - it just duplicates that row that it already has - it will disregard any non-PK columns from the view!

So in your case, once it has read a first row with the "stand-in" PK, any further rows with those values will just get the already read values - no matter what's stored in the database!

SOLUTION: as I said before: every table ought to have a proper primary key to uniquely identify each individual row - and in your view definition, you should include all primary key columns from all tables involved in defining your view into the result set of that view. With this, EF will hopefully automagically pick the right columns as its "ad-hoc" primary key and will actually read the values as returned from the RDBMS in your view.

user_133020
Related