.net - In Linq To Entities - How To Do An Inner EXISTS (SELECT 1 FROM - Type Of Query

Mar 6, 2011

I am trying to write a LINQ equivalent of

SELECT C1, C2, C3
FROM T1
WHERE T1.C4='xyz' AND
EXISTS (SELECT 1 FROM T2
WHERE T1.C17 = T2.C24)
ORDER BY C3

I'm using EF CTP 5, so I have a DBContext variable named dbc, which includes DBSet objects T1s and T2s, based on POCOs T1 and T2.

In LINQ I write

DIM IND = From i In dbc.T1s
Where i.C4 = "xyz"
And (From t In dbc.T2s Where i.C17 = t.C24).Any
Select i.C1, i.C2, i.C3
Order By C3

Running the query I get the error message "Unable to create a constant value of type 'T2'. Only primitive types ('such as Int32, String, and Guid') are supported in this context." When I omit the inner expression (third line in the LINQ code), the query runs fine. I tried switching the orders of the inner comparison, to be t.C24 = i.C17, with no effect.

ADVERTISEMENT

LINQ To Entities Does Not Recognize The Method [Type] GetValue[Type]?

Mar 14, 2012

I've a simple class like this:

Public Class CalculationParameter{
public Long TariffId{get;set;}
}

In a workflow activity, I've an Assign like this:

(From tariffDetail In db.Context.TariffDetails
Where tariffDetial.TariffId = calculationParameter.TariffId).FirstOrDefault()

Dto is passed to Activity as an Input Argument.It raise following error and I'm wondering how to assign Id.LINQ to Entities does not recognize the method 'Int64

GetValue[Int64](System.Activities.LocationReference)' method, and this method cannot be translated into a store expression.

How can I assign the calculationParameter.TariffId to tariffDetial.TariffId?!

UPDATE:Screen shot attached shows that how I'm trying to assign calculationParameter.TariffId to tariffDetail.TariffId (car.Id = Dto.Id) and the query result should assign to CurrentTrafficDetail object.

EF4: LINQ 2 Entities Query Works In C# But Not In VB

Dec 11, 2010

the query below throws a System.NotSupportedException: "Unable to cast the type 'System.Linq.IOrderedQueryable1' to type 'System.Linq.IQueryable1'. LINQ to Entities only supports casting Entity Data Model primitive types." The exception is only raised in the VB.Net version. When translated to C#, no exception is raised.

Dim doesThisCrash = From outerOrder In orders
Where outerOrder.ProductId =
(From p In products Join o In orders On p.Id Equals o.ProductId

[Code]....

Linq To Entities Query With Child Objects?

Oct 13, 2011

Basically I have the follwing:Dim ctx As New AdminCoreEntities Dim roles = (From r In ctx.Roles where r.Name.StartsWith("cust") Select r) 'list of System .Linq.IQueryable(Of AdminCoreModel.Role)

[code]...

The error i get when i run this is: Unable to cast object of type System.Data.Objects.ObjectQuery`1[AdminCoreModel.Role] to type AdminCoreModel.Role

Basically I have a many to many situation and I try to get all the Quicklinks objects queried by their roles and not quite sure why EF will cast to a single AdminCoreModel.Role when i.Roles is a collections of objects.

.net - Detect Empty Results In A Linq-To-Entities Query?

May 29, 2009

The only way I know of is awkward:

'check for empty return
Dim count As Integer = (From s In myEntity.employee Where employeeID = myEmployeeIDVariable).Count
'If there is a record, then process

[code]....

Update One Entity Based On Results Of Another LINQ-to-Entities Query

Apr 9, 2011

I have the following LINQ-to-Entities query:[code]This will give me all the resident assignments for the current year/term. Then I have this LINQ-to-Entities query:[code]This will give me all the rooms. I want to iterate through the assignments and based on what room is assigned update the occupancy in reset_occupancy. I'm not 100% sure how to accomplish this. Here is my pseudo code of what I want to accomplish:[code]

Update One Entity Based On Results Of Another LINQ-to-Entities Query?

Dec 15, 2009

I have the following LINQ-to-Entities query:

' Get all the residency assignments that match the term/year.
Dim assignments = From p In dbContext.Residents _
Where p.semester = term _

[code].....

Convert A Two-table Exists Query From SQL To Linq Using Dynamic Fields In The Subquery?

Jun 7, 2011

I'm trying to query old Access database tables and compare them with SQL Server tables.They often don't have primary keys, or they have extra fields that had some purpose in the nineties, etc., or the new tables have new fields, etc.I need to find records - based on a set of fields specified at runtime - that are in one table but not another.So, I do this kind of query all the time in SQL, when I'm comparing data in different tables:

dim fields_i_care_about as string = "field1, field2, field3"
'This kind of thing gets set by a caller, can be any number of fields, depends on the
'table
dim s as string= ""

[code]....

It tells me it can't convert a Boolean - Is there any way to do this without Linq expressions? They seem far more complex than what I'm trying to do here, and they take a lot of code, and also I can't seem to find examples of Expressions where we're comparing two fields in a subquery.Is there a simpler way? I know I could do the usual EXISTS query using JOIN or IN - in this case I don't need the query to be super fast or anything. And I don't need to use a DataTable or DataSet - I can put the data in some other kind of object.

Check If LINQ Entity Of Unknown Type Exists In Its Respective Table

Sep 23, 2010

I'm trying to check if a LINQ Entity exists in its table, but at design time I dont know what type that entity is. So I figure I'll just get the table, and try the Contains method on it. But I cant get the table in such a way that I can query it at design time.I've tried the GetTable method on the datacontext, but I dont know how to cast it to the appropriate type when using GetTable(Of). GetTable(Type) works, I just use Entity.GetType(), but then I don't know how to query the ITable thats returned.To try and cast the ITable to something useable, I created an interface(IWhatever) that could implement properties that are native to all of my entities I would encounter. [code]

Filter Parent / Child Table In Linq Query Based On Entities In Child Table?

Jul 9, 2010

I have a table (Projects) which is linked to projectVersions on projectID..projectVersions contains several columns on which I'd like to filter a returned project (and associated projectVersions) list. For example there is a "capacity" column and a "country" column. I am doing a filtered list of projects on one page and I'd like to include all projects where any one of the associated projectVersions has a capacity of 750ml and a country of "France" for example.It may be that a particular parameter is not set and so I pass a zero to indicate not to filter on that.I guess this needs some kind of subquery as when I try and do something like this: [code] it doesn't work as the "xxx" bit, being one-to-many, expects me to specify an item to get at the entities within and yet I want to query where ANY of the associated versions match one of the criteria.

Linq To Entities. GetPropety(name).GetValue Not Recognised By Linq?

Apr 20, 2011

I have a listbox on my xaml form that I bound to a List(Of MyType) property. I populated this list like so:

Dim fields As List(Of CheckableFields) = New List(Of CheckableFields)
Using context As ITIPEntities = New ITIPEntities()
Try[code]....

Now I'm at the point where the user selects the fields they want included in a report and I need to iterate over the required fields. This is my linq query:

For Each checkedField In _requiredFields
If checkedField.IsChecked Then
If checkedField.FieldData IsNot Nothing AndAlso checkedField.FieldData.Trim IsNot String.Empty Then[code].....

Linq To SQL Query To Select Maximum Value

Jun 21, 2010

Linq to SQL query to Select the maximum "MeanWindSpeed" value? The database name is "WeatherArchives" The TableName is "TblValues" The Column is "MeanWindSpeed" And Also, I would like to have a query to get me the Max "MeanWindSpeed" in each year,

Select Distinct In Linq Query?

Jan 4, 2012

I've a collection with the data like this.[code]...

how can select the distinct data using linq?

Select More Than One Column In A Linq Query?

Nov 2, 2009

Dim MyQuery = From c In xdoc.Descendants() _
Where c.Attribute(OriginY) IsNot Nothing _
Order By Val(c.Attribute(OriginY).Value), Val(c.Attribute(OriginX).Value) _
Select c.Attribute(UniStr)

Right above you can see my First! linq attempt! And here comes my first question.

How can i select more than one column in a linq query in vb.net?

For example... Select c.Attribute(UniStr) AND c.Attribute(OriginY)

Asp.net - Select Query In LINQ Based On Foreign Table

Mar 2, 2011

I have 2 Tables , OrderDetails and Requests In my LINQ to SQL dbml file. OrderDetailsID is a foreign key in Requests Table.

I want to write the query to get the sum of UnitCost from OrderDetails based on OrderId. And If there is a row in Requests Table for each OrderDetailsID, and the Requests.Last.RequestType="Refund" I want to reduce the total refund amount from the main sum otherwise If there is no row based on OrderDetailsID, add to sum.

Here is the way I implement that. I am looking to prevent using "For each".

iRefund = (From od1 In dc.OrderDetails _
Where od1.OrderID =1 _
Select od1.UnitCost).Sum

[Code]....

Select Query In LINQ Based On Foreign Table?

Mar 4, 2009

I have 2 Tables , OrderDetails and Requests In my LINQ to SQL dbml file.OrderDetailsID is a foreign key in Requests Table.I want to write the query to get the sum of UnitCost from OrderDetails based on OrderId.And If there is a row in Requests Table for each rderDetailsID, and the Requests.Last.RequestType="Refund" I want to reduce the total refund amount from the main sum otherwise If there is no row based on OrderDetailsID, add to sum

.net LINQ Xml Query - Anonymous Type?

Oct 22, 2009

I'm out of my depth populating a windows form from an XML linq query (see code further down in post). The listbox is populated as intended, when a value is selected (row) I would like to populate the remaining form controls. The linq query I believe has created a new datatype "Anonymous Types". The query is only local to the load form subroutine but I'm not sure how to make it global.

When I define the query simply as an object the code in the selectedValueChanged event can't execute due to late databinding. how I can initially load the listbox from linq xml query and then populate the other controls when the value changes? Am I right in thinking even though the listbox is multi-columns (property) you can ONLY ever reference a row and NOT a column? I'm new to LINQ and Anonymous Types and the more I read the more confused I seem to get. On a less important note the order by is not functioning as intended (no errors) but data is always retrieved in xml file order and not by the node <text>. Also looked at using <text>.first to order the results without success.

[Code]...

Use Custom Type In A LINQ Query?

Jun 4, 2009

If you have custom type, and you wish to assign a List(of T) to that custom type[code]...

Return Type For Collection From LINQ Query?

Oct 15, 2010

I have a method, that returns a group of accounts

Public Shared Function GetAllNotesByUser(ByVal UserID As Guid) As Account (??)
Using db As New MyEntity
Dim query= (From A In db.Account _

[Code].....

I would then like to pass this to another function to calculate the totals for all the accounts in the collection. Is it best practice to return an Ienumerable, a generic list, I'm just not sure what works best with LINQ and the entity framework.

Pass A Parameter From The Select List Into A Function For Joining A Linq Query?

May 25, 2010

I have a query that can be summarised in SQL as follows;

Select
S.StockCode
From
StockToCheck As S

[Code]....

Where the S var is a list of strings. This gives the error 'S' is not declared and points to the S in the function call / join (GetPOSStock). So it does not seem possible to do this in Linq, can anyone confirm?

Determine The Type Of Object To Use To Store A Linq Query?

Jul 3, 2010

I am using VB .Net for this, so I don't have access to var or this would be a simple matter.Right now my query is as follows

[code]...

So I used this query in LinqPad to help me determine what the object would look like. I got back a IOrderQueryable(Of RSError) which then contained a IGrouping(Of String, RSError) for each grouped collection of objects returned by the query.However I ended up with the current object type of errors as IOrderedQueryable(Of IGrouping(Of String, RSError)) because of the cast error I am getting in VS.

[code]...

I'm not sure how to get rid of the VB$AnonymousType_1 Part of the returned object.Am I even on the right track here or am I missing something completely?

C# - Joint Query In Linq To SQL To Select All Records Even There Are Some Null In The Columns Of Foreign Keys?

Feb 2, 2011

I'd like to take all suggestion either in C# or VB.NET.I have a DB diagram like the image below. I also include the database script here [URL]In the Students table, CountryId and RoomId column are allowed null. Because some records do not have info about room and country yet.Also, some students do not have essays.I'm doing a joint query with all tables. I want to select all students to project the result like this:Wanted query result.

Here's my current query that gives the result like the image below:

Dim db As New DBDataContext
Dim query = From st In db.Students _
Join c In db.Countries On c.Id Equals st.Id _
Join r In db.Rooms On r.Id Equals st.RoomId _

[code].....

current query result I got only one result back because I have (2) William NoMan record in every table. But I don't get anything about others, like (3) Sync Master who has everything but RoomId.What do I need to modify the query above so it will give me all students like in the wanted query image above?

.net - Linq Expressions With Linq To Entities?

Feb 28, 2012

The pattern I'm trying to avoid is checking if a string (normally a control's text value) is null/empty, and if it is, comparing it using Contains to a field in my data. Obviously the field isn't hard-coded into my extension, neither is the object type.What I've got works perfectly in Linq to Objects, but I get the generic run-time error "LINQ to Entities does not recognize the method 'System.String Invoke(GenericQueryHelper.Customer)' method, and this method cannot be translated into a store expression." when using an entity framework model.

Here's what I have:

<System.Runtime.CompilerServices.Extension()>
Public Function CompareAndFilter(Of T)(source As System.Linq.IQueryable(Of T), expressionField As System.Linq.Expressions.Expression(Of System.Func(Of T, String)), compareTo As String)

[code]....

I want my usage to look something like this:

Dim results = repository.Customers.CompareAndFilter(Function(c) c.FirstName, searchText)

I do need to get this running against a SQL database really, as it is filtering results, so I don't want to be doing that in memory.

How To Use The Vb Equivalent Of ++ For An Index Property In A Linq Query Projecting A New Type

May 3, 2011

I'd normally do this in C# but since I've got to get this code in this particular assembly which is a vb.net one, I'm stuck.

Here's my linq query:

Dim i As Integer = 0

Dim oldAndCurrentIntersectionOnNames = From currentApplicant In currentApplicants _
Group Join oldApplicant In oldApplicants _

[CODE]...

You'll see the .Index = i+=1

This was my attempt to do what I'd quite happily do in C# (i.e. Index = i++) in VB. Unfortunately the VB compiler doesn't like that. how I'd do this in VB.

Only Access Properties Of An Anonymous Type When I Add ToList To End Of My Linq Query

Dec 16, 2011

I'm learning LINQ and VB and just spent an hour trying to access the fields of an anonymous type defined in a linq query. The key (I learned) is to convert the query to a list before you try to iterate through it with a for loop. How to access property of anonymous type in C#?

This does not work: edit (this compiles, but intellisense does not recognize the type)[code]...

ASP.NET MVC - Linq Query With Count Returns Anonymous Type - How To Display In View

Sep 29, 2011

So I'm writing a query as follows:

Dim assSummary = From a In db.Assignments
Join ur In db.UserRegions
On a.Origin.ID Equals ur.Region.ID

[code]....

In the controller I can return the data easily as follows:

For Each c In assSummary
MsgBox(c.Description & " " & c.AssCount)
Next

If I pass the object through to the view using Viewdata("assSummary") = assSummary, how do I display the data? Every method I've tried results in messages about 'VB$AnonymousType_7(Of Integer,String) and I don't know how to retrieve the data from the anonymous type.

Inner Join In Linq To Entities?

Apr 23, 2009

Given two tables, customer and orders, how would one do a linq query to entities to find any customers with open invoices started before a certain date?

Asp.net - Concatenating String In LINQ-to-Entities?

Apr 7, 2011

This code works if I take out

" " + p.MIDDLE_NAME

Thus the remaining code looks like:

p.FIRST_NAME + " " + p.LAST_NAME

Maybe LINQ-to-Entities just doesn't support concatenating more than two strings at a time?

Protected Sub btnFilter_Click(sender As Object, e As EventArgs) Handles btnFilter.Click
Dim dbContext As Campus6Entities = New Campus6Entities

[Code]....

Databind To A Treeview Using Linq To Entities?

Feb 12, 2010

I have seen other similar questions dealing with WPF Almost exactly the same but I can;tfigure this out in ASP.net. I have a Pages table with a parentpage foreign key and want to databind them to a treeview. In the past I have created a hierarchicaldataset from a dataset but would like to stay within the entities framework if I can

Get CurrentTime From SQL Server With LINQ/Entities?

Sep 14, 2011

I am using LINQ with Entity Framework, and I need to get the SQL server timeCan anyone tell me how can I create a method so it retrieves the current server time?

Copyrights 2005-15 www.BigResource.com, All rights reserved