1

I was reading that linq was lazy and that it did not executed the query until it needed to.

if that is the case why does this code fails:

var db = new Data.DataClasses1DataContext(@"Data Source=.\sqlexpress;Initial Catalog=MyDb;Integrated Security=True");

var companies = db.Customers.Where(x => x.Company=="Foo");

var query = companies.ToString();

if I run that code in a computer that does not have sql server installed it will not run why? I am not doing any statement that needs data. If I would call companies.ToList() then its ok for the code to fail. Is there a way I can make use of Linq to SQL Classes without using a connection. I know the moment I do ToList() or try to enumerate through the results I will get an error. I just want to use Linq to Sql Classes in order to generate the SQL statements and see them as a string.

I have a client and a server. The server is a WCF service and the client is a console application. I will send the query encrypted for cases where the user is not entering it. I will like to generate my queries using Linq to Sql classes it does not make sence I have to install sql server on the client just so that I can generate the queries.

My temporary solution is to create a second database on the same server. That database will be allowed to accept remote connections and the whole purpose of it is so that the line

var db = new Data.DataClasses1DataContext(@"some remote connection string");

works. Once I initialize that line I will never need the connection again. It makes no sense.

Tono Nam
  • 34,064
  • 78
  • 298
  • 470
  • 2
    I don't believe that companies.ToString() will return the SQL query. It should return the result of the query, in this case it is IEnumerable.ToString() – evanb Feb 22 '14 at 00:37
  • If you don't know how to write SQL Statements, but you do know how to write Linq2Sql Lambda expressions, it would be insanely easier to use a product like [LINQPad](https://www.linqpad.net/) to write code and then see what the SQL statements would look like. – Erik Philips Feb 22 '14 at 00:37
  • `companies.ToString()` will generate the sql statements. Moreover I could extract the sqlCommands to be executed on db using `DataContext.GetCommand`. Plus its so much faster and safer in my opinion to build the queries with Linq instead of having them in a string – Tono Nam Feb 22 '14 at 00:40

2 Answers2

1

The problem is in the creation of the db context object and not in the linq statement. Specifically, in order to create the db context object you need an actual connection string. If you don't provide one, then the db context you try to create, I suppose it would be null or you will get an exception. Then defining your linq query using this null object will throw an exception, even if your query doesn't use the ToList(), which will definetely force the execution of your linq query.

Reading again your post I believe that you should define in the connection string the sql express server that is installed in the server, which will host the WCF service. Then the client having this connection string would have the ability to make calls to your server database.

Christos
  • 53,228
  • 8
  • 76
  • 108
  • How can I fake the creation of a db context? I will never use it – Tono Nam Feb 22 '14 at 00:30
  • This smells of an [XY Problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). What are you trying to solve by faking something you aren't going to use? What is the purpose of creating SQL statements if you aren't going to use them? – Erik Philips Feb 22 '14 at 00:31
  • I do not know how to generate SQL statements therefore I want to use linq. I want to use linq to sql classes just to generate the sql statements. I will then hand those to the wcf service. I will never execute those statemetns in the client – Tono Nam Feb 22 '14 at 00:33
  • I understand what you are meaning. However I don't see a way through which you will be able to create the queries you want without having a proper db context class and in order to do so you would need a proper connection string, which will point to an actual database. Using the connection string you have written above, it is required that a sql express server would be available on the machine you will try to execute your code. – Christos Feb 22 '14 at 00:37
  • +1 thanks a lot for the help! Could I use a local DB? If I deploy my project with a local database I will require the client to install sql server express? – Tono Nam Feb 22 '14 at 00:41
  • @TonoNam thanks for the vote! I think your problem will be solved in an easier way if you follow my new edit. Please let me know if that's clear to you. – Christos Feb 22 '14 at 00:43
  • If I enable the clients to connect to the database directly will that be unsecure. That means I will have to deploy the database password? all the queries are select statements so when they arrive to the server I validate if they start with select and they should not have escape characters – Tono Nam Feb 22 '14 at 00:44
  • You know what you are right I will change the mode of authentication and I will enable the client to perform select statemetents connecting directly. In order to update a entity then he will have to use the WCF service. – Tono Nam Feb 22 '14 at 00:47
  • I think that's the right way. Furthermore, I would suggest you to create a table of clients to your database and if the client doesn't provide the right credentials, then he/she will not get access to the service. Depending on the number of your clients, you will be able to create and roles for your clients to limit their access to your database. When I am referring to limiting their access, I mean to provide them with the ability to query only the database, or to make an update etc. In addition to the above having designed properly your clients table, you will be able to monitor also the use. – Christos Feb 22 '14 at 00:51
1

Do not generate queries on the client then pass the SQL to the service. Instead, generate the lambda expression on the client, and send the expressions to the service.

See "How can I pass a lambda expression to a WCF service?".

One problem this will solve is that of database and schema versioning. TO do it your way would require that the client understand the database schema and even database version, and that it be the same (or compatible) with that which the service uses. Otherwise, you would be stuck having the SQL for one version of SQL Server generated on the client, then sent to a different SQL Server version on the service (or equivalently, a different database schema).

Community
  • 1
  • 1
John Saunders
  • 160,644
  • 26
  • 247
  • 397