LINQ generates incomplete query

Question

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?

If you have to specify the database, you are connecting to the wrong one. Also, LINQ2SQL and Entity Framework are different products, please specify only the one you are using — Camilo Terevinto, Mar 25 '18 at 22:47
it's just linq generated query doesn't specify database name if click select top 1000 row on the same table generated query includes database name linq generated one doesn't. so what am i doing wrong? — giorgi, Mar 25 '18 at 22:57
@giorgi SSMS can include it because when you right-click a table/view and select "view top 1000 rows", it knows which database the table belongs to. When you remove the database pointer in the query, it will still work because SSMS also sets the connection's/script's default database to that same database. You can see/set which database your current query/connection/script is pointing to by looking at the drop-down menu at the top-left of the SSMS window. example: https://imgur.com/a/Uuq4l. The entity framwork has no way to know which database a table belongs to. — Guillaume Mercier, Mar 26 '18 at 00:16
thanks a lot Guillaume Mercier changing database from the drop down worked — giorgi, Mar 26 '18 at 11:47

Guillaume Mercier · Accepted Answer · 2018-04-25T19:50:20.407

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.

score 0 · Answer 2 · answered Mar 25 '18 at 23:17

0

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

answered Mar 25 '18 at 23:17

Glenn

96
1
4

Usually, you set the connection string in your app/web.config and override the default constructor in your context to point to said connection string. Having to set it every time isn't just a pain but also a lot of repeated code. Example: https://imgur.com/a/PXD74 – Guillaume Mercier Mar 25 '18 at 23:57

LINQ generates incomplete query

2 Answers2