How can I convert sql to linq

Question

This is my SQL query

SELECT 
sys.sysobjects.name Name,
sys.foreign_keys.*
FROM 
sys.foreign_keys 
inner join sys.sysobjects on
    sys.foreign_keys.parent_object_id = sys.sysobjects.id
WHERE 
referenced_object_id = OBJECT_ID(N'[dbo].[Country]')

I have installed Linqer to convert SQL to linq. But I got an error:

SQL cannot be converted to LINQ: Table [foreign_keys] not found in the current Data Context.

I am a beginner in Linq. Can Anyone help me to convert SQL to Linq

stackoverflow.com/questions/296972/sql-to-linq-tool and stackoverflow.com/questions/1339732/convert-sql-to-linq-to-sql and stackoverflow.com/questions/12238423/… — imonas
– imonas, Commented May 22, 2013 at 7:21
imonas:I alrweady have Linqer and linqpad.But am getting error 'SQL cannot be converted to LINQ: Table [foreign_keys] not found in the current Data Context.' — Mizbella
– Mizbella, Commented May 22, 2013 at 7:26
The problem is that system views will not be picked up by Linqer. If you want to read these tables in your application, first create your own views on them, as was done here and write a query on these views. — Gert Arnold
– Gert Arnold, Commented May 22, 2013 at 8:40
@GertArnold :-Can u please give me little more explanation... — Mizbella
– Mizbella, Commented May 22, 2013 at 9:06

Community · Accepted Answer · 2017-05-23 11:57:23Z

1

The problem is that system views will not be picked up by Linqer. If you want to read these tables in your application, first create your own views on them, as was done here and write a query on these views.

CREATE VIEW SysObjectsView AS SELECT * FROM sys.sysobjects;
GO
CREATE VIEW SysForeignKeysView AS SELECT * FROM sys.foreign_keys;
GO

SELECT obj.name Name, fk.*
FROM SysForeignKeysView fk
INNER JOIN SysObjectsView obj ON fk.parent_object_id = obj.id
INNER JOIN SysObjectsView objfk ON fk.referenced_object_id = objfk.id
WHERE objfk.name = N'Country'

Linqer should be able to pick up these views.

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered May 23, 2013 at 11:54

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mizbella Over a year ago

Is it possible to get related table names using linq in mvc3.The code i posted works well,but wat chnges i have to make to work it in mvc3

Gert Arnold Over a year ago

Make the views part of the linq-to-sql context.

Mizbella Over a year ago

got an error when i post your code in Linqer "Error Compiling Expression: Too many characters in character literal "

Collectives™ on Stack Overflow

How can I convert sql to linq

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related