linq-to-sql

Stuck on LINQ to SQL Query

I can't seem to translate this to LINQ: select stuff FROM history INNER JOIN profiles ON history.username = profiles.username LEFT OUTER JOIN files ON history.fileid = files.filename LEFT OUTER JOIN streams ON streams.identifier = history.fileid LEFT OUTER JOIN galleries ON galleries.identifier = history.fileid, su...

SQL Server login failure in middle of application run

I have an application that suddenly started throwing the following exception: System.Data.SqlClient.SqlException: Login failed for user 'username'. The username and password are correct and the app does some queries/inserts using the same login before throwing the exception. What are some other reasons a login can start failing? ...

How Would I Write This In LINQ2SQL?

I am slowly porting over an app from MySQL to use Linq2Sql - but one query has stumped me a bit. SELECT * FROM Pages WHERE DomainID = @reportid AND (PageContent REGEXP 'display\:[ \t]*none') > 0 ORDER BY URL ASC Any ideas on how I would write something like this with Linq2SQL? Its the REGEXP bit thats got me stumped? ...

Update asp.net listview with LINQ, programmatically

I'm trying to find a good code sample to update a database entry in my listview control. I suppose I would need to extract the ID from somewhere (some label control?). I am using LINQtoSQL to talk with the database. protected void lvTargets_ItemUpdating(object sender, ListViewUpdateEventArgs e) { InventoryDataContext...

Getting Entity Behind Selected Row From DataGridView with Linq To Sql

What is a graceful/proper way to retrieve the Linq entity behind the selected row of a DataGridView? I am populating my DataGridView like this in the form Load event: this.Database = new MyAppDataContext(); var userList = from c in this.Database.Users orderby c.LastName select c; thi...

Change two rows simultaniously

Context: ASP.NET MVC 2.0, Linq-to-Sql, .Net 3.5, IIS7, MS SQL 2008 I'm working on a game fan site. I have a table as follows: ToonId int (primary key) RealmId (FK into Realms table) OwnerId int (FK into Users table) ToonName nvarchar(50) IsMain bit That is a single user may own multiple toons on multiple realms (aka servers), but ex...

programmatically loading a crystal report

I am trying to load a crystal report which is supposed to load a report according to the date chosen by the user. I have written the code below to help me implement this but when I choose the date using a datetimepicker (dtpDate) an click the load button (btnReport) the compiler throws an exception that it cannot load the report. Can u p...

Why will linq to sql not allow a meber with an "override" inheritance modifier in the where clause?

Hi, I'm getting a InvalidOperationException when trying to run this code: database.Produkts.SingleOrDefault<Produkt>(x => x.Kod == "123") It only occurs when the Kod property has an override modifier. I need it this way because the entity class is derived from another class that resides in a separate assembly. Here's how the classes...

Issue with linq2sql with this specific stituation

Software used: Visual studio 2008 professional with services pack 1 Sql Server 2005 Standard Edition (9.00.4266.00) Windows XP SP3 I have these 3 tables: GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Table_2]( [table2id] [int] IDENTITY(1,1) NOT NU...

Linq 2 SQL, cannot create association

I'm working with a bit of a legacy database, but here's the basic idea: Item Id INT PK NOT NULL Description VARCHAR(50) NOT NULL TranslationId INT NULLABLE Translation Id INT PK NOT NULL Language VARCHAR(10) PK NOT NULL Translation VARCHAR(50) NOT NULL Basically every Item MAY have many Translations, seems simple enough. These tables...

LINQ-to-SQL "Member access not legal on type" exception with unioned and compiled query

I have multiple queries that I'd like to union together, then compile the entire thing. The uncompiled query runs fine, but an "InvalidOperationException: Member access 'Int32 Id' of 'UserQuery+Foo' not legal on type 'System.Linq.IQueryable`1[UserQuery+Foo]." exception is thrown when the same query is compiled and run. How do I fix thi...

LINQ-to-SQL join with composite key using "in"

I need to perform a left outer join between two tables using a primary key and an "in" clause. Desired SQL SELECT F.Id, B.Id FROM Foo F LEFT OUTER JOIN Bar B ON B.Id = F.Id and B.Name in ('Spam', 'Eggs') LINQ Query from f in dataContext.GetTable<Foo> join b in dataContext.GetTable<Bar> on f.Id equals b.Id into b1 from b in b1.Defaul...

Entity Framework - load data from table with 1:1 table involved using one query?

Hello, I have the following command: var query = from x in context.FirstTable.Include("SecondTable") where x.TestColumn == 5 && x.SecondTable.SecondTestColumn == 3 select x; Now I also want to load the entries from a third table named "ThirdTable". But I can only reference it via the SecondTable table. There is a foreign key from Fir...

Select all customers and their last order Linq to SQL

hei, Using Linq to SQL (ala NorthWind database for example): How to select all customers and the latest order for each of them. The customers who have not placed order should be also in the result. The last order could be by ID (ID is incremental) or Timestamp (DateTime field). similar to this http://stackoverflow.com/questions/33136...

Queries generated by group by vs group join

I have the following group by linq statement from c in Categories join p in Products on c equals p.Category into ps select new { Category = new {c.CategoryID, c.CategoryName}, Products = ps }; However this generates the following left outer join query and returns all categories even if there are no products associated. SELECT [t0].[C...

Linq-to-sql scalar stored procedure, most direct way to get single result?

I am using the following method and looking for input on better methods or more direct methods (less code, clearer purpose): _dc.StoreProcedureName().ToList().First() Thanks ...

Linq-to-SQL Help - Selecting Duplicate Rows

So this is what I am trying to do: select subscriber, count(subscriber) from subscribers where subscriber = subscribedTo group by subscriber having count(subscriber) > 1 Easy enough in SQL. I can't figure out how to write this in LINQ. Anyone know how to go about this? The table is just three cols, ID, subscriber and subscribedTo. ...

How to join with multiple (OR) conditions in LINQ to SQL?

How would I perform this SQL query Select Distinct s.* from #ScopeIDs x Join Scopes s on s.ScopeID=x.ScopeID or x.ScopeID is null in LINQ to SQL? (This query would return all Scopes whose ScopeID is present in #ScopeIDs, unless one of the entries in #ScopeIDs is null, in which case it returns all Scopes). A "literal" translation d...

Loading related objects without multiple database calls

I'm just learning LINQ to SQL and have run into the following obstacle. I have a the following 3 tables: PC (PCGUID, ParentPCGUID, ModelName, RetailerGUID) Offer (OfferGUID, ParentGUID, Title) Retailer (RetailerGUID, Name) With the following relationships: PC 1:N Offer Retailer 1:N PC Retailer 1:N Offer PC 1:N PC (self refere...

Apply dynamic where clause to joined table

I need to build a LINQ query that allows me to vary the where clause on a joined table but can't find a way to do it. A simplified example of the two queries I'm constructing are: var myQuery = from tab1 in context.Table1 join tab2 in context.Table2 on Table1.ID equals Table2.Table1ID where tab1.TypeCode == ...