"Where Not In" in LINQ to EF4

Is there a direct translation between this SQL Statement to LINQ?

Select ProductId From ProductReport Where ProductId NOT IN(Select ProductId From ClientProduct Where ClientId = CAST('06ae6be1-ca94-44c9-bd30-f1d4f3ac3264' AS uniqueidentifier))

I can achieve the same thing with two LINQ queries, but I'd rather use one query and hit the database once.

-------------Problems Reply------------

var q = from p in ProductReport
where !ClientProduct.Any(c =>
c.ClientId == new Guid("06ae6be1-ca94-44c9-bd30-f1d4f3ac3264") &&
p.ProductId == c.ProductId)
select p.ProductId;

Presuming you have navigations in your model -- and you should.

var clientId = new Guid("06ae6be1-ca94-44c9-bd30-f1d4f3ac3264");
var q = from pr in Context.ProductReports
where !pr.Clients.Any(c => c.ClientId == clientId)
select pr.ProductId;

Category:sql Views:0 Time:2011-06-27

Related post

  • How can I use external expressions in Linq with EF4 (and LINQKit)? 2010-03-30

    I want to separate out often used expressions in linq queries. I'm using Entity Framework 4 and also LINQKit but I still don't know how I should do it the right way. Let me give you an example: Article article = dataContainer.Articles.FirstOrDefault(

  • LINQ to EF4 Query Where clause not filtering as expected 2012-04-24

    I've googled for this but can't find anything obviously related, so posting here for some insight. Using LINQ / EF4. Here's the code snippet: private const string JOB_FAILED = "Failed"; // other code.. var successfulJobs = context.Jobs.Where(x =>

  • Unexpected Behavior from a Linq to EF4 Contains Method 2011-03-18

    On a query of people, this statement should return a number of results that have "And" in their last name- var results = repository.GetQuery().Where(p => p.Names .Select(n=> n.LastName) .Contains("And"); It returns no results. If we change it t

  • Having trouble nesting Lambda with LINQ in EF4 2011-08-12

    I'm getting the following exception when trying to perform the following LINQ-query nested with Lambda. Unable to create a constant value of type . Only primitive types ('such as Int32, String, and Guid') are supported in this context. If I remove th

  • ASP.NET MVC Search Page - Integer StartsWith On Linq + EF4 2011-03-27

    So, in my last post I was asking how to build a dynamic search filter using LINQ and EF4 (See Here) and finally came up with the solution of building the expression as a string and parse it to an expression using the Dynamic LINQ library. I that solv

  • Can someone explain why these two linq queries return different results? 2010-02-15

    I have two linq (to EF4) queries, which return different results. The first query contains the correct results, but is not formatted/projected right. the second query is what i want but it missing some data. Schema Query 1 var xxxx = (from cp in _con

  • SELECT INTO with EF4 2010-11-05

    I'm new to c#, linq, and EF4, so please bear with me. I'm sure it's something really simple, but not seeing it. The stored procedure I'm replacing does a SELECT INTO to query a bunch of data, do some simple transformations, and then output the result

  • How to add a table to the EF4 Context dynamically in code - No Code First 2011-02-18

    We run a series of reports every 6 months and store the results to tables that can be queried/viewed at any time in the future. Depending on the cycle either two or four tables will be added. They have a standard naming convention of yyyy_mmm_Table_x

  • Linq - How to take the result for a query in Linq and add it to an Array 2011-02-24

    I use c#, linq and EF4 I would like ask your help. My question: I have a linq query, but i need the result for this query be inserted in an array string. Value added should be Title and ContentId (ContentId from EF is an INT but i need it as a string

  • Linq and EF on .Any() 2011-03-15

    I use c#, linq and EF4. I have two tables in my DataBase represented in my Conceptual Model: DataBase Tables: CmsContents CmsCategories CmsRelatedCategories (Pure Juction Table) Entity Type: CmsContent CmsCategory Entyt Set: CmsContents CmsCategories

  • LINQ Query that is able to identity a Row in a Junction Table with two variables - How to do it? 2011-03-15

    I use c#, linq and EF4. I need help to write a LINQ query that is able to identify a Row in a DataBase using two variables for a Composite PRIMARY KEY. Here tables in my DataBase represented in my Conceptual Model: DataBase Tables: CmsContents CmsCat

  • How to convert a LINQ query from query syntax to query method 2011-03-15

    Linq and EF4. I have this Linq query in query syntax I would like convert into query method. Are you able to do it? I tried more tha 2 hours without success :-( Thanks for your time CmsContent myContentObj = (from cnt in context.CmsContents from cate

  • LINQ - How to write a query to set a variable bool True or False 2011-03-17

    I use asp.net 4 linq and EF4. I have this query where CmsSourcesContents is a navigational property. At he moment when I run the query the result for queryCheck is a type IQuerable. I need to valuate the condition express in my Linq query but as a re

  • LINQ - EF - FROM or JOIN 2011-07-11

    I use c#, linq and EF4. I have two tables in my DataBase represented in my Conceptual Model: DataBase Tables: CmsContents CmsCategories CmsRelatedCategories (Pure Juction Table) Entity Type: CmsContent CmsCategory Entyt Set: CmsContents CmsCategories

  • Linq - EF : How to query a Junction table? 2011-07-12

    I use c#, linq and EF4. I have two tables in my DataBase represented in my Conceptual Model: DataBase Tables: CmsContents CmsRelatedContents (Junction table) Entity Type: CmsContent I have some Navigational Properties: for CmsContent --> CmsConten

  • Better performances using Linq and Anonymous Types? 2011-07-19

    I use C#, Linq and EF4. I have a front end for my website, I need display (read only) data from my data base on my web pages. I would like to know what could help me to achieve faster (read only) results to my users. Here some idea I'm thinking to im

  • Convert Linq Query Expression to Method Syntax equivalent 2011-07-19

    I use LINQ, C#, EF4. I have this query expression in Linq. I need to convert in a equivalent in Method Syntax but I have some doubt on the struction. Could you provide me a good example. Thanks for your help. var myContentsForAuthor = from c in conte

  • Multiple left joins in LINQ query C# 2011-12-20

    I need help with a LINQ query that has multiple left joins. This is LINQ to EF4. I wrote the SQL and converted it to LINQ not knowing that the 'join' in LINQ is an inner join. I've looked all over for an example but can only find examples with one le

  • Many to Many Query in Entity Framework 4 2011-04-07

    I have have a many to many relationship in my database. The two end tables are BlogPost and Item and the table in the middle is ItemBlogPost. I need to get back all of the BlogPosts related to a specific item. In SQL I would do it like this: SELECT B

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.269 (s). 11 q(s)