SingleOrDefault linq method throwing exception

Question

I am using wcf rest service. I am querying sql server database to return me rows from the table name which i pass from GUI. What happens is that sometimes table doesnt exist in database and i am using singleorDefault method of Linq. So if there is no table in database then it throws exception. How could i handle this? I still need to perform certain logic even if table is not found. Here is my code

var strTableQuery = "Select * from " + tableName // i get this tableName from my GUI

Tables = Context.Database.SqlQuery<TableClass>(strTableQuery );  
var x = Tables.SingleorDefault(); // i get exception on this line

if (x != null){
    // do some logic
} else {
    //do some logic
}

Update1 I get exception as Invalid object name tableName

Why would you be working against an incomplete schema? That sounds like the thing to fix, to be honest... — Jon Skeet, Aug 15 '13 at 14:16
That's not an exception that `SingleOrDefault` throws. Which means before `SingleOrDefault` even begins executing, it's already failing. Something wrong with your query. — sircodesalot, Aug 15 '13 at 14:21
Also, SQL Injection: `"Select * from " + tableName`. (http://technet.microsoft.com/en-us/library/ms161953(v=sql.105).aspx) — sircodesalot, Aug 15 '13 at 14:22
@sircodesalot see my update in question. I fails because table doesnt exist in database — James, Aug 15 '13 at 14:22
No no, I mean your query is susceptible to SQL injection attacks - which is a major security vulnerability. Please read the corresponding link. — sircodesalot, Aug 15 '13 at 14:25
@sircodesalot i am passing it using json object. So i think should be no issue with sql injection — James, Aug 15 '13 at 14:30
@Happy: Do you mean it's completely impossible for an attacker to make a request using JSON which specifies a `tableName` value which causes a SQL injection attack? This should really be checked against a whitelist of appropriate tables, IMO. — Jon Skeet, Aug 15 '13 at 14:42
@Happy try passing `sys.objects;shutdown` as the table name in your json and see what happens. — Cristian Lupascu, Aug 15 '13 at 14:43
@w0lf at moment i dont have to care because its used internally. Nothing to outside cruel world :) — James, Aug 15 '13 at 14:44
@Happy: It's worth being *very* aware that a lot of IT attacks happen within companies rather than just outside. I would strongly recommend you to try to harden this as far as possible. If you check for the present table names to start with, this should be relatively simple anyway... — Jon Skeet, Aug 15 '13 at 14:57
@Happy: As per Anand's answer - load all the table names to start with, and then you can tell whether `tableName` exists before you execute anything. That should help against both SQL injection attacks (unless someone crafts a horrible table name *and puts it in the schema*) and solves your immediate problem. — Jon Skeet, Aug 15 '13 at 15:20

Anand · Accepted Answer · 2013-08-15T15:16:36.537

2

Fire this query before and check if your table exists or not.

SELECT count(*) FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = 'SomeSchema' AND  TABLE_NAME = 'someTable'

Update

Create a table called as Table_Existing_In_Your_Schema(name varchar, create_on datetime);

Load this table in a static dictionary in your global.asax.cs

Dictionary<string, DateTime> _allTablesCollection;

Writing a common module (something like dal) to fire all your query. In this module, before firing query

if(!_allTablesCollection.keys.Contains("someTable"))
     createTable("someTable");
     update_the_dictionary_with_this_key

//now fire your query

edited Aug 15 '13 at 15:16

answered Aug 15 '13 at 14:24

Anand

14,545
8
32
44

I am aware about this but is there any other alternative? – James Aug 15 '13 at 14:25
Catch sql exception and do what ever you want do :D – Anand Aug 15 '13 at 14:25
`try { ... } catch (Exception) { /* shrug */ };` Problem solved. +1 btw. – sircodesalot Aug 15 '13 at 14:27
so you i have to put 2 try catch blocks. Because i already have try catch block in my method – James Aug 15 '13 at 14:28
Well the correct approach is as suggested by Jon. Why incomlete Schema. You need to fix the problem at base. If not, then second approach is to use query to find whether table exists or not. If that also not works do a try catch at what every level you want to do....2 -3 does not matter, because the whole thing is now shit :D – Anand Aug 15 '13 at 14:31
one should respect others requirement. At the moment you dont know why i have to do like this. Anyways thanks for your suggestions. I will give you +1 efforts – James Aug 15 '13 at 14:37
@Happy: "At the moment you dont know why i have to do like this." That's why I asked you the question - your response didn't tell us *why* you had to do that; you just asserted that you did. If you'd provided more context in the question, it would really have helped to avoid all of this. – Jon Skeet Aug 15 '13 at 14:43
@JonSkeet But still i dont think its good way to call this as shit. – James Aug 15 '13 at 14:49
@Happy: I agree on that front - but I still think it's a bad idea to ask a question with an obviously very unusual context, but not provide any justification or details. I would suggest finding out *once* which tables exist, and avoid trying to use any absent tables within LINQ queries. Given how unusual your requirement is, I don't think you should be surprised that it's relatively painful to support. – Jon Skeet Aug 15 '13 at 14:55
@JonSkeet actually if i use approach of finding tables in schema then it slows down the process because for every tablename passed it has to hit database to find if it exist. I had that line mentioned by Anand about select * from information_schema , but i removed it as it was slow. – James Aug 15 '13 at 14:59
Slow it will be, but why the tables are not existing. Can you explain your design for this. Probably that could be changed – Anand Aug 15 '13 at 15:02
@Anand Actually we get tables from other system and we create our own tables based on it. But somehow one table is missing . This will be created later but will take sometime. Hence i dont have any other option. Do you still have better solution? Also just forget about my words i used since now you asked me very nicely – James Aug 15 '13 at 15:06
@Happy - if that's the case why not have a static dictionary in which you maintain all the tables that you have created. So before querying, it's just matter of peeking in to dictionary to see whether table exists or not and dictionary are awesomely fast in doing this . If not then go and create it else fire the query. You will need some kind of singleton behaviour here for creating tables as there could be concurrent calls for creating same table. There could be other better design to this. This was from top of my head – Anand Aug 15 '13 at 15:09
Even better, create a table which will hold information of all the tables that you have created. load it in to static dictionary so that you do not loose changes on iisreset or app pool recycle. My assumption is number of tables are not very large that you have to create – Anand Aug 15 '13 at 15:10
@Anand nope didnt understood what do you meant? – James Aug 15 '13 at 15:13
I have updated with my thinking. May be @JonSkeet could suggest something better. – Anand Aug 15 '13 at 15:17
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/35523/discussion-between-happy-and-anand) – James Aug 15 '13 at 15:28
@Anand ok no issues. I think i have to use query to check if table exists. Accepting your answer. Many thanks – James Aug 15 '13 at 15:29

SingleOrDefault linq method throwing exception

1 Answers1