FAQ Database Discussion Community


in Entity framework I want to include only first children objects and not child of child(sub of sub)

asp.net-mvc,entity-framework,entity-framework-4,linq-to-entities,entity-framework-5
I have these two classes: public class BusinessesTBL { public string ID { get; set; } public string FirstName { get; set; } public string lastName { get; set; } public ICollection<OffersTBL> OffersTBLs { get; set; } } public class OffersTBL { public int ID { get; set; } public...

Display the correct Label in jquery AutoComplete with LINQ

c#,jquery,asp.net-mvc,linq,linq-to-entities
I'm testing out the auto complete feature with jQuery, however I'm having trouble displaying the correct label. When the search involves only 1 field or criteria, it is simple: var model = db.UserProfiles.Where(u => u.Department.Contains(term)) .Take(10).Select(r => new { label = r.Department }).Distinct(); return Json(model, JsonRequestBehavior.AllowGet); However, if I want...

Linq to Entities - Filter on any item in one list belonging to another list

linq,linq-to-entities
Here is my scenario. I have a Document class. The document is associated with a DocumentClasses table through a many to many relationship, so a document can have one or more classes. When running a search, the user can choose to filter documents by class. So, I need to be...

EF LINQ - Return entities that contain an entire collection

c#,asp.net,entity-framework,linq-to-entities
I am trying to troubleshoot the following LINQ Query: public JsonResult SearchNodesByTags(string[] tags) { var nodes = _DbContext.Nodes. Where(n => n.Tags.All(t => tags.Contains(t.DisplayName))) .Select(n => new {n.NodeNativeId, n.NodeName, n.NodeClass.ClassName}) .ToList(); return Json(nodes); } The query is returning a single node that is not associated with a tag. What I want...

Getting a Dictionary from a Query with GroupBy

c#,linq-to-entities
Given two persisted entities public class Header { public virtual int Id { get; set; } public virtual string Name { get; set; } public virtual List<Detail> Details { get; set; } } and public class Detail { public virtual int Id { get; set; } public virtual Header MyHeader...

EF 6.x Code First: Different SQL Generated for Navigation Property - What am I doing wrong?

entity-framework,ef-code-first,linq-to-entities,poco
I'm a veteran web developer with plenty of T-SQL and schema designing experience, but, fairly new to EF (skipping right to EF 6!). I'm wondering what's wrong with my POCOs that I'm getting different SQL generated for the following two queries. Basically, I have two objects, "Parent" and "ChildThing" with...

Entity framework average , group by query

c#,asp.net-mvc,entity-framework,linq-to-sql,linq-to-entities
Hello i have the following query var myList = (from p in db.Full where (p.date_reception > begin & p.date_reception < end & !p.mc_object.Contains("NULL") & (!strListe.Contains(p.mc_object))) group p by new { p.mc_object} into g select g.OrderByDescending(p => new {p.duration,p.mc_object} ) into r select new StringIntType { str = r.mc_object, nbr =...

How to access anonymous type projected in Linq query in the next chained extension method?

linq,c#-4.0,linq-to-entities
I have a collection of Schemas, where each Schema has Name, Id and several other properties. I need to project just the Name and Id of every Schema and thus I am creating an anonymous type in the LINQ query as follows: from s in db.Schemas select new { Id...

How to aggregate more than two entities using Linq for a SelectMany?

c#,linq,entity-framework,linq-to-sql,linq-to-entities
I have a database with multiple tables that I am trying to "flatten" into one table on another database. I am able to easily do this with Entity Framework using only two tables (or entities), but trying to add in 3 or more gives me compile errors. There's got to...

Cannot resolve method between Enumerable and Queryable candidates

c#,lambda,linq-to-entities
I have this method in a class called Invoice: public static Expression<Func<Invoice, bool>> IsAllocated() { return i => i.TotalAmountDue == i.GetAllocationsTotal(); } I have a list like this: IQueryable<Invoice> invoices And I need to filter it like that (it's Linq to Entity): var filteredInvoices = invoices.Where(i => Invoice.IsAllocated()); In this...

Method cannot be translated into a store expression syntax error

c#,linq,entity-framework,linq-to-entities,linq-to-objects
I'm using LINQ to Entity and getting error Method cannot be translated into a store expression var myStrings = from keys in keyTable join values in valuesTable on keys.ID equals values.FK_TableKey select new NewModel { Value = values.Value, Hash = CalculateHash(string.Format("{0}_{1}", keys.Key, keys.Context)) }; //I tried to convert in into...

“LINQ to Entities does not recognize the method 'Boolean ***, and this method cannot be translated into a store expression.”

linq,asp.net-mvc-5,linq-to-entities,linq-expressions
I am coding a MVC 5 internet application, and am getting the following error: base = {"LINQ to Entities does not recognize the method 'Boolean IsAccountSetupForSendingFreeTrialSubscriptionExpirationEmail(CanFindLocation.Models.Account)' method, and this method cannot be translated into a store expression."} Here are my methods: public IEnumerable<Account> GetFreeTrialAccountsForSendDailyExpirationEmails(int minimumDaysLeftInSubscriptionForEmail) { IEnumerable<Account> freeTrialAccounts =...

Entity Framework inline SQL dynamically select table name

linq,entity-framework,linq-to-entities
I have a less than optimal database that I am querying which strangely, instead of storing look up items in a parent child relationship, stores them in individual tables. So, for example, there is a table for countries and one for states etc. I cannot change the database structure, keeping...

how to combine this linq query to a combobox

c#,combobox,linq-to-entities
i have two tables course which has CourseID and Name, and Section which has SectionID, CourseID, price, time. i have the following code and i dont know how to use desiplaymember and displayvalue of a combobox. thanks in advance var fillcmb=(from crs in re.Sections from r in re.Courses where crs.CourseID...

I need convert query from sql to entity to linq

sql,linq-to-entities
I am using this query in SQL but cannot convert having from TSQL to Linq select COUNT(*) AS DD ,FCourseName from TCourse GROUP BY FCourseName HAVING COUNT(*) > 1 ORDER BY DD DESC iam try this query var result1 = from history in context.TCourses group history by history.FCourseName into g...

Multiple CheckBox Selection Result using Linq without hardcoding

c#,asp.net-mvc,linq,asp.net-mvc-4,linq-to-entities
Illustration of database (actual column and table name is different) EmployeeeID IsSicked IsRetired IsHoliday 1 false false true 2 true false true 3 true true true 4 false false false What problem am I having? I have a checkbox for IsSicked, IsRetired and IsHoliday in my application, which allow user...

LINQ multiple from clauses null value

c#,linq-to-entities
Hi I'm fairly new to this, hope you can help I found the following SO article which shows how to filter a LINQ query LINQ to SQL selecting all records which have any word in the string split What I can't figure out is how to return all results if...

Left outer join linq query object reference not set to an instance of an object

c#,linq,linq-to-entities
I have three table to which i performed inner join on two table and than the left outer join Why the below query is returning me this error "linq query object reference not set to an instance of an object" var model = from p in Uow.Instance.RepoOf<RoleMenuMetrix>().GetAll() from n in...

Remove duplicate records using LINQ [duplicate]

c#,asp.net,linq,linq-to-entities
This question already has an answer here: LINQ: Distinct values 5 answers I want to use linq to object to remove duplicate records. I want that final list will contain unique records with the latest Date. I have the following list AId BId C(date) **1 2 24/5/2015** 3 6...

Linq to query data between 2 dates when within a group

c#,sql-server,linq,datetime,linq-to-entities
I am trying to query my MS SQL database, using Linq. The code is var ids = (from o in _visitDetail group o by o.CompanyId into grouped where (grouped.Max(s => s.DateTime) - grouped.Min(s => s.DateTime)).TotalDays > 0 select grouped); This results in an exception being thrown: "DbArithmeticExpression arguments must have...

Using dynamic linq in EF Linq to Entities

c#,entity-framework,linq-to-entities
I'm building up a Linq.Expressions.Expression at runtime and then I thought I would use it like so: Type myTypeVariable = viewModel.typeVariable; DbContext db = //get entity model for myTypeVariable Expression myExpression = //build a predicate of myTypeVariable from viewModel var query = (IQueryable<myType>)db.Set(myTypeVariable); var results = query.Provider.CreateQuery(myExpression); The error I'm...

EF Intersect Syntax

c#,entity-framework,linq-to-entities
A UI allows users to select one or many tags. I would like to select all Nodes that have ALL that tags the user entered associated, not just a single tag. public JsonResult SearchNodesByTags(string[] tags) { var dbTags = _DbContext.Tags.Where(t => tags.Contains(t.DisplayName)).ToList(); var nodes = _DbContext.Nodes.Where(n => n.Tags.Intersect(dbTags).Any()); // Error...

c# Intersect a List with List of entities

c#,linq-to-entities,tuples
I have to find the coincidences in two lists, one of a specific entity and the other is a tuple The entity is very simple, like this: public class SUBJECT { int id; int group; } The coincidences must be with both properties of the SUBJECT entity, and the two...

Calculate DiffDays function DateTime order - Does the larger DateTime come first or second

c#,linq-to-sql,asp.net-mvc-5,linq-to-entities,datediff
I am coding a MVC 5 internet application, and I am wanting to write a function that will work with both a SQL statement as well as with a List. Here is my code: public static class TestableDbFunctions { [System.Data.Entity.DbFunction("Edm", "DiffDays")] public static double? DiffDays(DateTime? dateValue1, DateTime? dateValue2) { if...

dynamic fields for EF model ~ there has to be a better way

c#,dynamic,linq-to-entities
Using MVC5, EF6 Model First Issue at hand: DYNAMIC ( user created ) Fields that i need to store in the database. I've rammed my head against the internet all day today trying to get something better than writing alter table, update, and select commands for dynamic fields. I don't...

Linq to entities subquery

entity-framework,linq-to-entities,entity-framework-6
How can I write the following subquery in Linq: Context.Set<Process>() .Include(...) .Where(x => x.Activity.Name.CompareTo(Context.Set<Activity>().Where(a => a.Id == activityId).Select(c => a.Name)) > 0) .Take(1); This is a simplifed version of query, WHERE clause only includes the part that is not working. If I change Context.Set().... subquery to a string constant, then...

How to perform a join of two sets and populate navigation properties

c#,asp.net,linq,entity-framework,linq-to-entities
Let's say I have two classes, Teapot and Cup: public class Teapot { [Key] [Column("Id", TypeName = "int")] public int Id { get; set; } public int MaterialId { get; set; } public int ColorId { get; set; } [ForeignKey("MaterialId")] public virtual Material Material { get; set; } [ForeignKey("ColorId")] public...

Want to sum fee in query month wise

c#,linq,entity-framework,linq-to-entities,linq-query-syntax
var TotalFee = (from item in _dbEntities.MonthlyFees where item.Year == DateTime.Now.Year && item.FeeStatus == true select item).GroupBy(x =>x.MonthID);//.AsEnumerable().Sum(x => int.Parse(x)); I want to sum Column "Fee" which is nvarchar type and group it by MonthID...

LINQ to Entities only supports casting EDM primitive or enumeration types error

c#,linq,entity-framework,linq-to-entities
There are probably dozens of questions with duplicate titles at SO, I apologize if this is a duplicate, but my limited experience with LINQ I was not able to find a resolution. I am trying to retrieve the last message, the FirstName and lastname, and date last message was sent,...

Giving a string a null value

c#,linq-to-entities,nullable
I am in need of some information on how I can set this string in the code snippet below to a null value. [Index(IsUnique = true)] [StringLength(450)] public string StockCode { get { return _stockCode; } set { _stockCode = value.ToUpper(); } //(ToUpper)<<-- Error } When I try to do...

Expression Tree for Aggregate Function in LINQ to Entities

c#,linq,entity-framework,linq-to-entities,aggregate-functions
I need to dynamically substitute an aggregate function on top of a LINQ to Entities query to perform the operation in database and in the same time to do not hard-code what function do I use, e.g. replace Average() with Max() or Min() in a query like this: IQueryable<Order> orders...

an object with the same key already exists in the object statemanager When trying to add the fallowing data Using EF

c#,sql,linq,entity-framework,linq-to-entities
I have the Relational Database as shown below on the EDM. When I am trying to submit a new F_Orden with two existing F_Producto_ProductoCompra and which has an exisitng FDistributor I get the Error mention on the title. What is the right way to do this? Please any help would...

Order by property using entity framework

c#,entity-framework,sorting,linq-to-entities
I want to write some API to sort entities on server side (SQLServer) using LINQ2Entities. I have class which contains expression represents sorting field of entity and sorting direction : public class SortOption<TEntity> { public SortOption(Expression<Func<TEntity, dynamic>> keySelector, bool ascending = true) { KeySelector = keySelector; Ascending = ascending; }...

How to define expression selection nested one-to-one relationship in Entity Framework

c#,entity-framework,linq-to-entities
I am trying to run a Linq-to-Entity that contains this Expression into Entity Framework. Not Working: //2 seperated expressions, 1st expression calling the 2nd expression public Expression<Func<User, UserDto>> UserToDtoExpr() { var expr = AddressToDtoExpr(); return x => new UserDto() { Id = x.Id, Name = x.Name, Address = expr.Compile()(x.Address) };...

How to count duplicated items

asp.net-mvc-4,linq-to-entities
ourProblems = Db.Problems.GroupBy(m => m.Title).Select(g => g.FirstOrDefault()) .Select(s => new ProblemsModel.Problems { Id = s.Id, Name=s.Name, Text=s.Text, }) So here I get duplicated items but I need the number with this one if there is 5 duplicated items it only gives me the name. I want it to give...

OR operator is not working in LINQ

c#,linq,entity-framework,linq-to-entities
I need to select a list which is not contains grade 2 and grade 8 . Now all the items with grade 2 also selects. Please see below is my code var subjectList = printViewModel.GetSubjects().Where(p => p.Grade != "2" || p.Grade != "8"); PrintTemplateViewModel class public class PrintTemplateViewModel { public...

OrderBy with SwitchExpression in Linq to entities

c#,linq,linq-to-entities,expression,expression-trees
I need make custom orderby for enum. I try use SwitchExpression: public static IQueryable<T> MyOrderByEnum<T>(this IQueryable<T> source, string propName, Type enumType) { var type = typeof (T); var property = type.GetProperty(propName); var parameter = Expression.Parameter(type, "p"); var propertyAccess = Expression.Property(parameter, property); var enumValues = Enum.GetValues(enumType); var switchCases = new SwitchCase[enumValues.Length];...

LINQ: Select all from each group except the first item

c#,linq,linq-to-entities
It is easy to select the first of each group: var firstOfEachGroup = dbContext.Measurements .OrderByDescending(m => m.MeasurementId) .GroupBy(m => new { m.SomeColumn }) .Where(g => g.Count() > 1) .Select(g => g.First()); But... Question: how can I select all from each group except the first item? var everythingButFirstOfEachGroup = dbContext.Measurements .OrderByDescending(m...

How to select all objects that have a property value in list of values?

c#,linq,linq-to-entities
I have a table named Items. Items have a property named "locationId" Given a list of location Ids, how do I select all items? List example List<long> locationIds = new List<long> { 1, 2, 3 }; Essentially the query below, but for multiple locations at once: var sleectedItems= db.Items.Select(i =>...

Build expression tree for LINQ using List.Contains method

c#,linq,linq-to-entities,entity-framework-4.1
Problem I'm working on refactoring some LINQ queries for several reports in our web application, and I'm attempting to move some duplicate query predicates into their own IQueryable exension methods so we can reuse them for these reports, and reports in the future. As you can probably infer, I've already...

How to refactor LINQ query

c#,linq,linq-to-entities
I have the following code: result = from i in _dbContext.Meetings where i.UserInvitedID == CurrentUserID && i.MeetingStatus == null && //!i.IsTex && DbFunctions.AddMinutes(DbFunctions.AddHours(i.MeetingTime.Day, i.MeetingTime.Hour).Value, i.MeetingTime.Minute).Value > dateWithTime //where i.UserInvitedID == CurrentUserID && i.MeetingStatus == null && DbFunctions.TruncateTime(i.AllowedTime.AllowedDate.Day) >= date select new ITW2012Mobile.Core.DataTable.MeetingModel2Tmp() { Name =...

Selecting multiple columns from each row and removing duplicates

c#,linq,linq-to-sql,linq-to-entities
Say I have a table with the following structure: - Id - Phone1 - Phone2 - Address This table has multiple records. What's the best way, using linq to SQL, of selecting all the phone numbers (from columns "Phone1" and "Phone2") in each row, and also removing repeated values. I've...

LINQ to Entities does not recognize the method in query

c#,linq,entity-framework,linq-to-entities
I have the following code that gives me an error saying that LINQ to Entities does not recognize the method GetShippingHour: private IEnumerable<OrderDTO> GetAllFixedOrders(DateTime date) { var clientSet = _context.Set<Client>(); var orderSubstitutes = _context.Set<OrderSubstitution>() .Include(o=>o.Order.Client) .ForCurrentDay(date) .Select(os => new { os.OrderId, os.FixedOrderId }); var orderList = _context.Set<FixedOrder>() .Include(fo => fo.Client)...

Group by ALL days in a range

c#,entity-framework,linq-to-entities
I have a requirement to have results returned from a query for every day in a range, even if the database has no results. Is there some groupBy trick I can do for this, or will I have to merge my results into a pre-generated empty results list? Edit: All...

Creating if statement inside a variable

linq-to-entities
I am trying to create an 'if' statement inside the coding below: var qisg = new QuoteItemSectionGroup { SectionGroup = db.SectionGroups.Where(x => x.Name == "Longitudinals" && x.Section == TruckSection.Floor).First(), StockItem = quoteItem.Chassis.Longitudinal, <<-- Here Quantity = 2, Length = globals.FloorCalculatedLength }; Example: if (quoteItem.Chassis.Longitudinal == "SCH100") Stockitem = quoteItem.BodyType.Longitudinal; Is...

Linq to entities query that returns a count and a list of objects

c#,linq,entity-framework,linq-to-entities
Is it possible to create a linq to entities query that will give me the total count of things in a query, along with a list of some of those items? For example if the query unconstrained would return 40,000 items but I just want the 5th page of 500...

Difference in child property declarations - using Id, property, both or neither [duplicate]

entity-framework,linq-to-entities
This question already has an answer here: What are Independent Associations and Foreign Key Associations? [duplicate] 2 answers I have a class called Team, that can have any number of optional TeamMembers. There appears to be 4 ways to declare it using EF / LINQ to Entities. If the...

Linq to entities group join on multiple tables

c#,entity-framework,linq-to-entities
I'm trying to perform a group join on multiple tables with one to many relations, using into instead of group by. But something is not right. I get duplicate records for each Role a user has. from compUsr in Repository.All<CompanyUser>() join usr in Repository.All<User>() on compUsr.UserId equals usr.Id join usrRole...

sql “not in” in Linq using datatable

sql,linq,linq-to-entities
How to convert sql query to linq using datatable. select DISTINCT targeturl from internal where TargetURL NOT in (select DISTINCT url from Internal) using this query, but still not getting the correct result. var List1 = dt.AsEnumerable().Select(c => c.Field<string>("URL")).Distinct().ToList(); var List2 = dt.AsEnumerable().Select(c => c.Field<string>("TargetURL")).Distinct().ToList(); var Targetname = List2.Except(List1.ToList()).ToList(); ...

Combining two LINQ queries that only their return result is different

c#,linq,linq-to-entities
I have a query that is a join to a few tables like TableA, TableB, TableC and TableD and at the end I am saying select tableA to work with its tableA roecrds that it returned. Then later on I have another query that its tables, joins and where clause...

LINQ Where clause inline for loop?

c#,linq,entity-framework,linq-to-entities,where-clause
I have database values called start and length. start is the start time of a booking (1->09:00, 2->10:00 etc) and length is the length in hours. i then have an array of start times and end times. I want to be able to check whether each start and end pair...

put where predicate in a variable

entity-framework,linq-to-entities
I am using Entity Framework code first and I have code like this: var foos = context.Foos.Where(f => f.Bar == "Hello World").ToList(); Using an SQL profiler, I see that the script run against the database contained a WHERE statement, as expected. But when I put the Where predicate in a...

'Only primitive types, enumeration types and entity types are supported' again

c#,entity-framework,linq-to-entities
I thought, that I understand it and will not have problems with it. But now I'm confused. I have the following code: public class ProjectMemberUserRolesElementViewModel { public string AccessType { get; set; } public bool Delete { get; set; } public bool Create { get; set; } public bool Edit...

Searching and splitting database values with search terms and querying using Linq

c#,linq-to-entities
I'm trying to figure out how to perform a search that maps a word from my search term to a word that's stored in a cluster of words(paragraph) in the database. This works pretty much ok, except that it also returns results with letters that are in the Contain and...

How do i conditionally add a list to a ViewData.Model if 2 data values from two different DB tables are equal in ASP.NET MVC 4?

c#,sql,asp.net-mvc-4,linq-to-entities,viewdata
I am attempting to create a conditional statment in ASP.NET MVC 4 and Entity Framework. I need to create a list of models where the ID from one table is equal to data in another model's table. How do I right this conditional statement using Linq? Below is the code...

How to use predicates with LINQ to query CRM 2011

linq,linq-to-entities,dynamics-crm-2011,predicate,linqkit
I am trying to use linqkit to Predicate. Getting following code when I am trying to compile. public void TestPredicate(Guid[] productIds) { var predicate = PredicateBuilder.False<Product>(); foreach (var productId in productIds) { var tempGuid = productId; predicate = predicate.Or(p => p.ProductId== tempGuid); } } var query = from p in...

Entity Framework - how to avoid writing duplicate queries when needing counts and then items

entity-framework,linq-to-entities
I have constructed a LINQ query that joins about a half dozen tables. The problem is, for paging purposes, I want to get a count first of how many items will be returned. So the issue I'm running into is having to write the exact same query twice: one to...

c# Linq to entities select case to a string

c#,linq-to-entities
I have to query a list of entities and concatenate the results in a comma separated string, taking the consideration of a property of the entity. I am using a foreach loop, but I am sure there is a better way using Linq string str= string.Empty; foreach (var item in...

Entity DbSet and Any() producing true on empty collection

entity-framework,linq-to-entities
I have a weird error I cannot understand. After setting breakpoints and local watches it came to this: How can _temp be true (i.e., the collection db.Users contains a user of name "dummy") although the collection db.Users is empty? For completeness: public class DBCUsers : DbContext { public DbSet<User> Users...

C# EF6 issue binding Linq result to DataGridView where date column is null

c#,linq-to-entities,entity-framework-6
All, I'm having an issue with the Linq query below: private void BindDataGrid(string _filter) { using (var context = new Kennels_Data_ModelContainer()) { var grid = context.Pricings .Where(x => x.AnimalType.Type == _filter) .Select(x=> new {x.PriceStart, x.PriceEnd, x.Price}); if (grid != null) { var results = grid.ToList(); pricingDataGridView.DataSource = results; pricingDataGridView.Columns[0].Width =...

Using custom methods in linq to entities

c#,entity-framework,ef-code-first,linq-to-entities
I have a Person table in my database that has NationalId field. Is there any way to load all Persons with even NationalId, using Ef code first and Linq to entities, without loading all Persons to memory? Somethings like: public bool IsEven(int number) { return number % 2 == 0;...

Count of childrens 1-* children *-* Linq Entity Framework

c#,linq,entity-framework,linq-to-entities,entity
I am a bit new to LINQ and EF and was trying to get the total count of students in each class taught be each teacher in the below database. I want to output the teacher name followed by the class name and then the count of students in that...

select basing on count

linq,entity-framework,linq-to-entities
I have a table Bill. This table have a field Id_Client. I want to recover a list with the Clients that exceed a number of bills. In SQL, I do something like this select Id_Client, Count(*) from MyDB.dbo.Bill group by Id_Client having Count(*) > 100 order by c desc Where...

Only primitive types or enumeration types are supported in this context- linq join

linq,linq-to-entities
I am getting this error when I try to join var users = _users.Get(); var userApprovals = (from approval in _entities.ApprovalEntities join userDetail in users on approval.UserKey equals userDetail.UserId where approval.EmployeeUid == employeeUid select new UserApproval { Id = approval.Id, EmployeeUid = approval.EmployeeUid, UserKey = approval.UserKey, UserId = approval.UserId, UserName...

Does Any() run the LINQ query

linq,linq-to-entities
If I write a query for example: var query = (from a in this.Context.Addresses where a.Active select a.Name); then is it correct to say if(query.Any()) return query.ToList(); return null; notice the query didn't have a FirstOrDefault() or ToList() so I am wndering if .Any() runs the query? ...

LINQ Confusion with Sum and comparison

c#,linq-to-entities
I am trying to determine what invoices still have a balance. I am going thru the invoices via Entity Framework and attempting to calculate which ones have a subtotal greater than the amount of payments applied. I have written the following 5 test cases var test1 = _invoiceRepository.Where(x => x.Subtotal...

SQL query with JOIN to LINQ

c#,linq,entity-framework,tsql,linq-to-entities
I'm having trouble translating following query to Linq: SELECT u.UserId, u.UserName, r.RoleName FROM aspnet_users u INNER JOIN aspnet_UsersInRoles ur ON u.UserId = ur.UserId INNER JOIN aspnet_Roles r ON r.RoleId = ur.RoleId AND r.RoleName IN ('SuperAdmin', 'AdminExtended', 'ExpertExtended') ORDER BY u.UserName I've tried with several Linq queries but I'm not sure...

Build Dynamic Expression For Linq Where using PropertyInfo and a value

c#,asp.net,.net,linq,linq-to-entities
As the title states, I would like to build a dynamic expression using propertyInfo and a value. Currently i have something like this public static IQueryable<T> WhereValueEquals<T>(this IQueryable<T> q, FieldFor fieldFor, string fieldValue) where T : ModuleData { switch (fieldFor) { case FieldFor.Name: return q.Where(e => e.Name == fieldValue); case...

Database for image tagging

c#,database-design,linq-to-entities
I need to design an image tagging database for a school project. The site will have Images, Tags and Users. Evey Image gets tagged with one or more of the tags (Tags are things like: summer, beach, Tyoko etc.), but I also need to keep track of the history of...

SQL “IN” CLAUSE Performance issue

c#,sql,linq,linq-to-entities
I have a simple SQL statement: select * from Employee where LEFT(REPLACE(EmployeeName,'.',''),4) IN ('ABCE', 'BCDS', 'EDSC', 'XDSD', 'EFSE') And I am trying to do this in Linq: Employees.Where(x => new[] { "ABCE", "BCDS", "EDSC", "XDSD", "EFSE" }.Contains((x.EmployeeName.Replace(".", "").Substring(0, 4)))); but the SQL statement it generates is not efficient. The EmployeeName...

Optimizing LINQ Query using Entity Framework

c#,asp.net-mvc,linq,linq-to-entities
I have a following LINQ query to get product information using Entity Framework productDetails.Items = (from productDetail in db.ToList() select new prod { ID = productDetail.ID ProdName = productDetail.ProductName, ... ... ... ... Calaculation1 = GetCalaculation(productDetail.Calc1), Calaculation1 = GetCalaculation(productDetail.Calc2), ... ... ... ... Calaculation15 = GetCalaculation(productDetail.Calc3) } ).ToList(); where the...

How to join and perform an operation between JSON data in C#?

c#,json,linq-to-entities
I'm dealing with linq-to-entities, querying 2 different entities. One returns data and the other returns addValues, also I have references to both NewtonSoft.Json and System.Data.Objets: var query1 returns data and var query2 returns addValues. Debugging I can see that: data = [{"key":"tf","value":221},{"key":"ba","value":108}]; addValues = [{"key":"tf","value":2},{"key":"ba","value":1.5}]; How do I obtain a...

Entity Framework AddOrUpdate implementation

c#,linq,entity-framework,linq-to-entities
I'm trying to create a extension method similar to AddOrUdpate. The usage should be like this: var student = context.Students.GetOrCreate( s => s.Name, new Student() { Name = "SomeName" }) If there is any user with that name it should return it, otherwise it should create the new user. This...

Get lastest record from section using mvc5

asp.net-mvc-4,asp.net-mvc-5,linq-to-entities,asp.net-mvc-5.2
I want to show in my home page: All section (all link category and one lastest news record in this section). Please help me to complete my code. Thank you so much. My DbContext class: public partial class xxDbContext : DbContext { public xxDbContext() : base("name=xxDbConnection") { } public virtual...

Linq to Entity - error on ICollection (code first)

c#,linq,entity-framework,exception,linq-to-entities
so We used a code first approach to build the database. I have come across an issue and am having very little success resolving it. the DB class (EF - code first) public partial class Object1 { [Key] public Guid uid { get; set; } public int sequenceNumber { get;...

dynamic linq to entities to comply with DRY

c#,asp.net,linq,linq-to-entities
Is there a way to do the below dynamically so I don't have to repeat myself? var result = from c in _Entities.Cars where c.Colour == "White" select c; var result = from m in _Entities.Bikes where m.Colour == "White" select m; etc... as in: var entities = new List<string>...

Entity framework DateTime to month, day and hour

asp.net-mvc,entity-framework,model-view-controller,linq-to-entities
I have this query that returns variable date_showx as dateTime var myList = (from p in db.Full group p by p.date_reception into g orderby g.Key select new { date_showx = g.Key, countx = g.Count() } ).take(10).ToList(); In my app I have a frequence button whose values are month, day and...

Linq to entities remove from list

c#,linq-to-entities,removeall
I want to remove items from a list of entities, when there is a concidence from a list (id). I have written this code, but I am guessing there is a better way to do it, and improve performance. Here is my code: List<int> toRemove; //includes the ids of the...

Linq to Entities filter navigation collection properties

c#,linq,entity-framework,linq-to-entities
I have an order class that has two navigation properties of type collections; OrderDetails and Categories. There is a one to many relationship between Order and both OrderDetail and Category. An Order may or may not have a Category associated to it. An OrderDetail record has a CustomerID field. I...

The entity or complex type … cannot be constructed in a LINQ to Entities query

c#,linq,entity-framework,linq-to-entities
Why does one method work but not the other, when it seems they are both doing the same thing, i.e. constructing an entity. My question then, is there a way to construct the entity in a L2E query instead of having to use just Linq or indeed both? This works...

How can i fill a value from one my domain model to my edit model?

c#,asp.net-mvc,linq,linq-to-entities
In my project i have : countries and CountryEditModel. public class countries { public int id { get; set; } public string Code { get; set; } public string Name { get; set; } } public class CountryEditModel { public int id { get; set; } public string Code {...

Use related entity multiple times in query

.net,vb.net,entity-framework,linq-to-entities,entity-relationship
I'm trying to get a list of two tables into one list of a generic class I made. My question is how I could get the fields from the related table without calling firstOrDefault for each field, please see sample code, I need a verly long line for fields Price...

dbcontext.entity.select not available in Visual Studio 2013

c#,asp.net,sql-server-2012,linq-to-entities,entity-framework-6
it's been a while since I work with EntityDataModel. I used to do things like: var listaProductos = dbContext.Productos.Select(p => new I just started a new web project with asp.net and added the entity data model but If i try to do the same as the example above I can't...

Split Flags into Normalized Table

c#,linq,linq-to-entities,linqpad
I have a table that looks like OldThing id | Value | Flags | ... int | varchar | int | ... ... | ... | 2 | ... ... | ... | 19 | ... ... | ... | 82 | ... ... | ... | 3 | ......

Linq To Entities in WebAPI query child entities based on parent fields in the case where URI parameters are optional

c#,linq-to-entities,entity-framework-6,asp.net-web-api2
I have two tables Schedule and ScheduleItems with a one-to-many relationship Schedule: ScheduleID (Guid) ChannelID (Guid) ScheduleType (string) IsComplete (bool) ScheduleItems: ScheduleItemID (Guid) ScheduleID (Guid) Title (String) ScheduledTime (DateTime) ContentType (String) From my webAPI controller for schedule items I would like to return the results of a query based on...

query multi-level entity with filter at the lowest level

c#,asp.net,asp.net-web-api,linq-to-entities
So I have 3 entity classes: public partial class Event { public Event() { Recurrences = new HashSet<Recurrence>(); } public int Id { get; set; } public ICollection<Recurrence> Recurrences { get; set; } } public partial class Recurrence { public Recurrence() { AspNetUsers = new HashSet<AspNetUser>(); } public int Id...

How to sum a single row with multiple columns in linq query

c#,linq,entity-framework,linq-to-entities,linq-query-syntax
(from item in _dbEntities.Bills where item.Year == year select item).GroupBy(x => x.MonthID).Sum(x => x.Phone); I want to sum all utility bills month wise like January there are 4 bills electricity, phone, water,gas.. these are in a single row in table how to get sum of these all and groupby...

Building an entity join LINQ query

mysql,asp.net,linq,linq-to-entities,entity-framework-6
I have the following table strutucture and am accessing them by using MySQL Entity Framework: Table Users - Id - Name Table Subscriptions - Id - Id_User - Id_Course Table Courses - Id - Name What I would like and am having a hard time to do so is building...