CODEWITHSUNDEEP
c#.netvisual-studioentity-frameworklinq
giorgi
giorgi

Reputation: 13

LINQ generates incomplete query

so i have this query

var pac = from p in context.pacientebi where p.personalid == piradinomeri select p;

it generates this sql 

SELECT
[Extent1].[id] AS [id],
[Extent1].[name] AS [name],
[Extent1].[surname] AS [surname],
[Extent1].[dateofbirth] AS [dateofbirth],
[Extent1].[personalid] AS [personalid],
[Extent1].[phonenumber] AS [phonenumber],
[Extent1].[sqesi] AS [sqesi],
[Extent1].[address] AS [address],
[Extent1].[shemosvlisdro] AS [shemosvlisdro],
[Extent1].[email] AS [email]
FROM [dbo].[pacientis] AS [Extent1]
WHERE ([Extent1].[personalid] = @p__linq__0) OR (([Extent1].[personalid] IS NULL) AND (@p__linq__0 IS NULL))

and doesn't return any value, if i change table name to include database name like this 

FROM [dbo].[pacientis]

to this 

[CachedeskBase].[dbo].[pacientis]

everything is fine. so why does linq to slq generate incomplete query?

Upvotes: 0

Views: 254

Answers (2)

Guillaume Mercier
Guillaume Mercier

Reputation: 401

I have an idea of what might be causing your problem;

You should check that your current Connection String contains a default catalogue parameter and possibly make sure that your application's database user default database is set.

Setting The initial catalogue/database variable in the connection string:
You might have miss typed or forgot to include a default catalogue/database in your connection string.

Including the default catalogue in your connection string tells your database server which database to use for your requests. (In this case, your LINQ/entity framework requests.)

Here is an example of a connection string containing a default catalogue: 

data source=[Your server's address];initial catalog=[Your database CachedeskBase in your case];persist security info=[set to false if you plan on using sql authentification, else true];user id=[Your default user];password=[your user's password];MultipleActiveResultSets=True;App=EntityFramework

Side note: in a production or even development environment you shouldn't include plain database usernames and passwords. I would recommend you look into ISS App Pools if you are planning on deploying this application. When using an app pool you do not specify a user for your application and therefore their passwords. The user id and password fields are replaced by integrated security tag

Setting the application's database user default database:
This action is not recommended as you should always have a default catalogue/database for your user that doesn't contain any potentially dangerous data. This is mostly for security as you want to add hurdles in the path of potential intruders. Making your application's database your user's default database should not be something you want to happen.

As I have a SQL server instance running, I'll be using SQL Server for my example.

In SQL Server Management Studio, go to the security folder and then in the logins folder of the object editor for your database and double click on your user. (If you are using IIS app pools, look for IIS APPPOOL\[your app pool name]).

This should open the login properties window, allowing you to set the default database of your user. SSMS login property editor pointing out where to find the default database

Upvotes: 2

Glenn
Glenn

Reputation: 96

You might need to specify your connectionstring to the datacontext as asked here.

Dim connectionString = "Data Source=myserver;Initial Catalog=mydatabasename;Integrated Security=True"
Using ctx As New HRManagerDataContext(connectionString))
          Return (From us As User In ctx.Users
                 Where us.IsActive = True
                 Select us)
End Using

Upvotes: 0

Related Questions