Working with a Read Only table without a primary key in Entity Framework

Question

Working with a client that has a readonly database and a table that has no primary key. I am attempting to compare one table, "item" (has a primary) with "Pics" [No key] in order to get results from "item" that have pictures. Whether or not each item has a picture in "Pics". I cannot edit the databases themselves in SQL.

My ultimate goal is to return the results that only have pictures.

I've tried to use a raw SQL Query and then convert it to an IQueryable, however this does not allow me to search items using other parameters and is EXTREMELY slow. (there are probably 75+ columns in the "item" table and 10,000+ total items) I can only figure out how to use the query with 'SELECT *'.

Controller:

entities db = new entities();

var results = (from s in db.items
                           select s);

...

var Pics = db.Database.SqlQuery<Item>(
"SELECT * FROM dbo.Pics
AS p JOIN dbo.item
AS i ON 
i.item = p.item
WHERE p.Pic = 'Yes'").AsQueryable(); 

...

results = Pics;

...

return View(results);

I don't understand `i.item = p.item`. Is that the primary key for item in the item table and the foreign key for item in the Pics table? — David Specht, Jun 25 '19 at 17:39
it is the primary key for item. If the table was designed correctly, it would be the foreign key for item under pics. However I'm working with a table that isn't correctly designed so I don't think it's technically a foreign key, though I'm trying to treat it as such (the data is the same for the two columns) — mmartiny, Jun 25 '19 at 17:47
So that should translate to LINQ very simply. Perhaps my [SQL to Linq Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you? — NetMage, Jun 25 '19 at 17:55
My issue is I'm not sure how to access the Pics table in entity framework without use of an SQL query due to it having no keys. The Pics table isn't compiling in my edmx. Is there a way to use LINQ with a table that isn't in a model? — mmartiny, Jun 25 '19 at 18:00
How was the edmx file generated, that made the Pics database or Pics table not to generate? did you use Visual Studio and the designer view to generate?. — Bosco, Jun 25 '19 at 18:07
I did. I updated the model from database, selected the pics table. It gives me the warning that it does not have a primary key defined and that the table has been excluded. — mmartiny, Jun 25 '19 at 18:09

score -1 · Answer 1 · answered Jun 25 '19 at 18:06

-1

Try this

var results = from i in db.items
              join p in db.Pics
              on i.item equals p.item
              where p.Pic == "Yes"
              select i;

answered Jun 25 '19 at 18:06

David Specht

7,784
1
22
30

I can't get the Edmx file to generate the Pics table due to it having no primary key, so 'db.Pics' won't work. That's where my main issue lies. – mmartiny Jun 25 '19 at 18:15
Yes, I see that now. Does it allow you to create a composite key of several columns? – David Specht Jun 25 '19 at 18:16
As far as the table and databases are concerned, I can't do anything with them. They are completely read only. I may be able to do something with them in my application, but I'm not sure how. The table does not show up in my designer when I update my model from database though. – mmartiny Jun 25 '19 at 18:22
Could you maybe use Code First from database instead? – David Specht Jun 25 '19 at 18:40
Once I converted to Code First the solution worked. So while I could have figured out the answer you posted on my own, I probably wouldn't have thought to convert to code first. Thank you so much! – mmartiny Jun 25 '19 at 20:09

Working with a Read Only table without a primary key in Entity Framework

1 Answers1