FAQ Database Discussion Community


NHibernate, expression trees, and eliminating repetition

c#,linq,nhibernate,linq-to-sql,expression-trees
We have implemented a security layer around our NHibernate persistence layer in a way that hopes to prevent a user from even receiving an object back from the database if he shouldn't have access to it. That security layer looks like this: public static IQueryable<T> Secure<T>(this Queryable<T> query){ //if T...

LINQ to SQL “cannot access disposed object” exception

c#,asp.net,sql-server,linq-to-sql,linq-to-objects
My code is throwing an exception when I attempt to access children of an object I retrieved from the database. I am using LINQ to SQL. using (var DC = new MyDataContext()) { TimeSlot timeSlot = DC.TimeSlots .Where(w => w.FacilityID == facilityID .OrderByDescending(o => o.LoadDate) .FirstOrDefault(); return timeSlot; } //...

NHibernate LINQ Join 2 queries to retrieve stocks with 4 recent vendors

c#,linq,nhibernate,linq-to-sql
I have to retrieve stocks and recent 4 vendors. I got stock in one list. Got purchase order with vendors in another list. Want to get a merge result of all stocks with their recent 4 vendors in purchasing if any. I am not good with LINQ and this is...

LINQ get Highest value and + 1

c#,linq,entity-framework,linq-to-sql
I'm newbie to LINQ. I will like to get know what's the highest value for 'Question Position' and i want increase it by 1 for new Question and save it into database from MVC 4 view. My db data : (highest position value is 2) ==================== Question | Position ====================...

Selecting Closest Prior Year Cash From DB

c#,asp.net-mvc,linq,linq-to-sql
I'm trying to select the cash (decimal) amount from the closest prior year (db column is called year, but is of DateTime type). This is giving me the cash from the year just input. Any suggestions for fixing my query? ViewBag.priorYearCash = ((from r in db.FinancialPeriods where r.Year.Year < financialPeriod.Year.Year...

Change DataContext of IQueryable

c#,multithreading,linq-to-sql,task-parallel-library,datacontext
I am working on a project that uses a static global DataContext (Which is not recommended but extremely hard to change at this point). I currently need to increase the performance of some parts by parallelizing some functions that are independent. Since DataContext is not thread-safe, I cannot use it...

How to insert data into table in linq to sql

c#,asp.net,linq-to-sql
i have a table TransactionUpdate with columns TransactionUpdateId, OrderNumber,BalanceAmount,TotalOrderAmount,LinkToCustomerMasterId,LinkToLocationId,LastUpdateDate i want to insert data into TransactionUpdate i have tried this. but only once data is being inserted. after that data is not inserted in table TransactionUpdate objTransaction = new TransactionUpdate(); objTransaction.OrderNumber = objBooking.OrderNumber; objTransaction.BalanceAmount = 0; objTransaction.OrderTotalAmt = objBooking.TotalOrderAmt; objTransaction.LinkToCustomerMasterId...

Linq2Sql Clone an Object C#

c#,asp.net,linq,linq-to-sql,clone
tblAgreement AgreementOld = (from g in TDC.tblAgreements where g.AccountID == ids.accountID && g.AgreementID == AgreementID select g).SingleOrDefault(); Guid AgreementIDNew = Guid.NewGuid(); var AgreementNew = AgreementOld; AgreementNew.AgreementID = AgreementIDNew; AgreementNew.StatusChangeDate = DateTime.Now; AgreementNew.CreationDate = DateTime.Now; AgreementNew.AccountID = ids.accountID; AgreementNew.CreatedByID = ids.userID; TDC.tblAgreements.InsertOnSubmit(AgreementNew); I am trying to create a new object with...

IQueriable failback to IEnumerable

c#,linq,linq-to-sql
Having IQueriable<T> returned from Linq-2-SQL data context is it possible to wrap it with custom IQueriable<T> implementation that on execution stage (for instance, while enumeration) will handle underlying IQueryProvider's NotSupportedException and failback to enumerating whole data context and applying Expression using in-memory IQueryProvider (System.Linq.EnumerableQuery<T>)? Code example: IQueryable<User> users = usersRepository.GetAll();...

Convert or output SSIS package/job to SQL script?

sql-server,linq,entity-framework,linq-to-sql,ssis
I understand this may be a little far-fetched, but is there a way to take an existing SSIS package and get an output of the job it's doing as T-SQL? I mean, that's basically what it is right? Transfering data from one database to another can be done with T-SQL...

linq and operator issue

c#,linq,linq-to-sql
i have query of linq to sql like this forms = attributesData.Where(x => x.strValue.ToLower().Contains(ddlProvince.SelectedItem.Text.ToLower()) && x.attributeName == "Province" && (x.attributeName=="District" && x.strValue==ddlDistrict.SelectedItem.Text) && (x.attributeName == "TrainingProgram Code" && x.strValue==ddlSearch.Text) && (x.attributeName == "Father Name" && x.strValue==txtform.Text) ).Select(t => t.strFormId).Distinct().ToList(); problem is i have only strvalue column...

c# OrderByDescending issue if DateTime is same

c#,linq-to-sql
I trying to sort list by DateTime in descending order. I have tried this. list.Records = list.Records.OrderByDescending(w => w.CreatedOn).ToList(); It give me correct record in descending order when CreatedOn is difference. But the issue occurs when there is same record CreatedOn time. In above image I have two record in...

how linked the DocX component with a Requette Linq ?

c#,linq,c#-4.0,linq-to-sql,c#-3.0
i use the component Docx in my app , and I want to display my word file from a Linq Requette http://www.codeproject.com/Articles/660478/Csharp-Create-and-Manipulate-Word-Documents-Progra

Linq to DataSet

c#,linq,linq-to-sql,jointable,asp.net-mvc-5.2
I want to pick up all Sellers that aren't boss of a department. How can I make this? In this query below, only the Sellers that are bosses of a department are picked up, I want the opposite of thereof. My query: var query = db.Sellers .Join(db.Departments, s => s.Id,...

Getting values from Session variable

c#,asp.net-mvc,model-view-controller,linq-to-sql,session-variables
I have this piece of code var myList = (from p in db.Full where ((p.date_reception > begin & p.date_reception < end & !p.mc_host_class.Contains("NULL")) & (!strListe.Contains(p.mc_host_class))) group p by p.mc_host_class into g orderby g.Count() descending select new { hostclassx = g.Key, countx = g.Count() }).Take(10).ToList(); HttpContext.Current.Session["allList"] = myList; i want to...

Querying and mapping complex objects using Linq-to-SQL

c#,linq-to-sql,automapper
I have the following two tables: ChildTable ID ManyColumns ParentID 1 XXXX 1 2 YYYY 1 3 ZZZZ 4 ParentTable ID Name 1 aaaa 2 bbbb 3 cccc 4 dddd I have the following classes representing the above tables: public class Child { public string ID { get; set; }...

Does Linq to Sql close connections automatically? [duplicate]

c#,linq-to-sql
This question already has an answer here: C# Linq-to-Sql - Should DataContext be disposed using IDisposable 1 answer Does Linq to Sql Close connections automatically? or I should use using? var db = new DataContext(); // Codes or using (var db = new DataContext()) { // Codes } ...

How does Caliburn.Micro and Linq-to-SQL work together

c#,windows-phone-8,linq-to-sql,caliburn.micro
I am using Caliburn.Micro and wonder if there is any convention or practice to work with a database and entities. I put all the database creation stuff in my AppDataContext which inherits from DataContext. Then I added a container.Singleton<AppDataContext>(); in my AppBootstrapper and inject the AppDataContext into my ViewModel constructors....

Can't set return type of a sql function to my class

c#,linq-to-sql
I have an MVC app which uses linq to sql. I just imported a function from the database to my dbml file. I also added a class that I want to use as a return type for this function. However when I look through the dropdown list for return types...

LinqToSql Query return time too performance too long

c#,sql-server,linq,linq-to-sql
I'm doing a Pretty hefty LinqToSql statement that returns a new object. Due to the amount of SQL Methods (Sum and convert mainly) the SQL is taking a significantly long time to run and therefore loading the web page takes a long time (10-15 seconds). While I could use AJAX...

How to select multiple fields from a DataView and apply .Distinct() using LINQ

c#,linq,linq-to-sql
I want to select some fields from DataView and after selecting those fields want to apply .Distinct() on these set of fields. Right now, I used this code : DataView dvGroups = new DataView(); dvGroups = GetDataFromDatabase(); //-- Fill Dataview var groups = dvGroups.Table.AsEnumerable() .Where(x => x.Field<int>("GroupId") != 0) .Select(p...

Clearing data context on error?

c#,linq-to-sql
I'm working on a program that uses Linq-to-SQL. There is a method in the program that bombs out because it's attempting to write a record with a duplicate key. I've added a try/catch on the context.SubmitChanges() and I'm logging the error so I can check it out later. My problem,...

Local database solutions for windows phone development

database,windows,sqlite,windows-phone-8,linq-to-sql
I am maintaining a simple set of records. I want to store these records as table on my Windows phone. I have come across Linq-to-SQL. My question is, is it the default database provided by the Windows phone system just as SQLite is provided by ANDROID? Is Linq-to-SQL used by...

Linq to sql ! operator for nullable of type int

c#,sql-server,linq,linq-to-sql
sql column ReadershipLevels - int with null level is local int type variable & is bitwise operator Linq to SQL vAdvanceSearchResults = from t in vAdvanceSearchResults where (t.ReadershipLevels & level) > 0 select t; if level = 32 Above Linq query generated the SQL like below: SELECT [t2].[ReadershipLevels],[t2].[ID], [t2].[ISBN], [t2].[IsHighlighted],...

Find All Rows in DataTable Where Column Value is NOT Unique Using Linq Query

c#,linq,linq-to-sql,dataset
I am attempting to write a Linq to SQL query that returns all rows in a DataTable where a columns value (TxnNumber) is not unique. So far, I have the Linq to SQL below where dt is the DataTable that contains the field TxnNumber. I think that I am pretty...

Web API 2 modify db in GET method

entity-framework,linq-to-sql,asp.net-web-api,asp.net-mvc-5,asp.net-web-api2
I want to modify some fields after query, but the return value is changed after I modified the fields. I want to keep the IQueryable value after I modify it. How can I store the IQueryable value? public IQueryable<Book> GetBook(int id) { IQueryable<Book> Books = null; IQueryable<Book> Book_return = null;...

Dynamic Linq select first in groups after GroupBy

c#,linq,linq-to-sql,dynamic-linq
I think I've read every post on this subject but still can't seem to get this to work. I want to duplicate the following GroupBy with dynamic Linq. I've tried this Dynamic GroupBy but can't seem to get the First(). var result = mydata .GroupBy(g => g.ID) .Select(z => z.First())...

How to check if data is added to a table in LinqToSql class and date to TextBox

c#,linq,linq-to-sql,windows-forms-designer
I have a assignment to do using LinqToSql class and Windows Forms. I need advice on how to check if data that the user entered is successfully added to a table and to write a message of success/failure to user. Also i can't figure out how to show date in...

Returning the class which is a foreign key in the database

wcf,linq-to-sql
I want to ask this question and I tried to search for a while without concrete answers. I have made a database and used LINQ2SQL to auto-generate the classes needed. I have set the serialization mode to unidirectional to make sure the classes are being serialized and making the datamembers....

Applying a function within the select clause

c#,linq-to-sql
I have a query (longish with joins etc) and for certain fields in the select clause (all of which are of type decimal?), I want to return null when the DB has them stored as 0. Doing the following work correctly: var q = from O in .... .... select...

ViewData displaying database column name

asp.net-mvc,linq-to-sql,viewdata
I've tried inputting some data into a ViewData called "Cost". This is taking a value from the "PrinterCreditsCost" column in a database, and should display the value of that column. However, the Column name is also displayed with curly brackets, so as opposed to just display "2" it displays "{...

EF6 linq to sql The context cannot be used while the model is being created

c#,multithreading,entity-framework,linq-to-sql
I have to System.Timers.Timer in my application. The intervals of those timers are 1000 and 120000. And each time they tick, they save some entity to database. But sometimes i get this error in my code; The context cannot be used while the model is being created.This exception may be...

LINQ: Count Users in 3 way join

linq-to-sql,count
I'm having trouble with LINQ to SQL, which I think should not be too difficult. In SQL I have a BusinessUnits, that get's divided in OrgUnits, and Users belong to an Org Unit. I want to print the BusinessUnitID with the number of Users in each. In SQL, it will...

EF LEFT OUTER JOIN instead of INNER JOIN in one to many relationships

sql-server,linq,entity-framework,join,linq-to-sql
I have the following EF code-first model public class A { public int Id { get; set; } public B B { get; set; } } public class B { public int Id { get; set; } public string Text { get; set; } } And these mappings modelBuilder.Entity<A>() .HasOptional(a...

Encrypt field value in LINQ-to-sql

c#,asp.net,linq,encryption,linq-to-sql
I'm trying to take a better encryption approach for the functionality of Person.FirstName = Encrypt(rawFirstName); Person.sensitiveInfo = Encrypt(sensitiveInfo); I'd rather not have to manually encrypt this kind of data every time it's used, so I've looked into modifying the .dbml and the Person object found in the .designer file. I've...

Compare two DateTime variables

c#,.net,linq-to-sql
I want to compare two Date Time variables. I have this in code of the old project: public int GetTotal(DateTime d) { using (rdbyDataContext2 Context = new rdbyDataContext2()) { var q1 = from qu in Context.SearchRequests where qu.search_date.Year.Equals(d.Year) && qu.search_date.Month.Equals(d.Month) && qu.search_date.Day.Equals(d.Day) orderby qu.user_name select qu; return q1.Count(); } }...

Query for data every hour

c#,sql-server-2008,linq-to-sql
I have a bunch of date data in a sql table like this: 2015-01-1 1:00:00, "some data" 2015-01-1 1:25:00, "some data" 2015-01-1 1:40:00, "some data" 2015-01-1 2:00:00, "some data" 2015-01-1 2:30:00, "some data" 2015-01-1 3:30:00, "some data" . . . 2015-01-31 23:00:00, "some data" 2015-01-31 23:50:00, "some data" I want...

Write a complex Linq2SQL query

c#,asp.net-mvc,linq,linq-to-sql
I have Following Data Category CategoryName CategoryID Product ProductID ProductName CategoryID Item ItemID ItemName ProductID What will be the query so that i will get multiple list i.e. ListOfCategory contains CategoryName and ListOfProduct<> ListOfProduct contains ProductName and ListOfItems<> ListOfItems<> Contains ItemName and ItemID var cats = (from g in CMP.tblCategories...

orderby () containing numbers and letters

c#,wpf,linq,linq-to-sql,sql-order-by
I want to order an array containing numbers and letters in an ascending order the values are stored as a string in a DB. This is the list I have 4B 1A 1 1B 2 4C 4 and want them to order like this where numbers are ascending followed by...

How LinqToSql Generates output result of stored procedures

.net,linq,stored-procedures,linq-to-sql
for example you have a stored procedure with title : sp_test when you drag into LinqToSql, it generates sp_testResult class with stored procedure output columns as its properties. I want to know how LinqToSql distinguish output result of stored procedure?...

Serialize Linq to SQL result into JSON (newtonsoft) in C# or VB.NET

json,vb.net,serialization,linq-to-sql
I have a plain and simple Linq to SQL query: dim result = from c in ctx.customers select c How can I use Newtonsoft.JSON to serialize the result using VB.NET (preferably) or c#? As I'm lazy as a programmer, I don't want to create a class for the result because...

SQL Join Query taking long to complete

sql,linq-to-sql
I am working on a project that require me to join four tables. I have written this code but it's taking forever to finish. Please help. Ohhh I have about 121 000 entries in the Db PortfolioCollectionDataContext context = null; context = DataContext; var Logins = from bkg in context.EnquiryBookings...

LINQ to SQL Left Join with empty left part

c#,sql-server,linq-to-sql
I have a method: public Result[] LeftJoin(IQueryable<AEntities> aEntities) { var bEntities = ReaderContext.GetContracts<BEntities>(); var queryable = from a in aEntities join bEntity in bEntities on a.BId equals bEntity.Id into bTemp from b in bTemp.DefaultIfEmpty() select new Result { A = a, B = b }; return ReaderContext.Select(queryable); } When I...

Linq2Sql select where string is NOT null or empty

vb.net,linq,linq-to-sql,isnullorempty
I'm trying to pull records from a table (with join) where a text field PassFail must not = PASS not be null/empty but I'm struggling with the syntax. The errors I have had so far point to the syntax I'm using is not supported by linq.. My latest attempt is...

LINQ TO SQL in WPF Application generating connection is closed exception, sometimes

c#,wpf,mvvm,linq-to-sql
My WPF (modern UI) application has the following architecture: I have a ViewModelBase class that all view models inherit from. This class has a property called DataAccessLayer of type DataAccessLayer. DataAccessLayer class has this definition: public class DataAccessLayer { private readonly DatabaseDataContext _context; public DataAccessLayer() { var connectionString = Settings.Default.DevelopmentConnectionString;...

Why do i get network error when i tried to access a database file(.mdf)?

c#,linq,linq-to-sql,sql-server-2008-r2
i have used following simple LINQ2SQL query to access an .mdf database file on hard drive and display certain rows class Program { static void Main(string[] args) { string str = @"D:\SE\MS\LINQ\LinqInAction\Data\NORTHWND.mdf"; DataContext db = new DataContext(str); Table<Contact> contacts = db.GetTable<Contact>(); var x = from y in contacts select y;...

Is possible to use lambda expression in linq to sql?

c#,linq,linq-to-sql,lambda
I wonder if you can do something like this: public List<T> FindOrder<T>(Expression<Func<T, bool>> predicate) where T : class { DbSet<Preventivos> preventivos = this.Preventivos; return (from p in preventivos where predicate select new... ...

What can I do to improve the speed of this query?

c#,linq-to-sql
I have a linq query that returns the last page a user looked at based on a table of page hits. The fields are simply TimeStamp, UserID and URL which are logged from user activity. The query looks like this: public static IQueryable GetUserStatus() { var ctx = new AppEntities();...

Linq to Sql with dynamic database

c#,linq-to-sql
I'm now creating an application which creates database in runtime. For example: User 'A' registers to my server will be provided a database A.sdf. After that, I want to create a connection to the newly created database file using Linq to Sql. I'm wondering if I'll create that connection during...

Linq to SQL : How to do WHERE [Column] = Element in LIST ?

c#,linq-to-sql
First of all, i've seen many posts like mine but I didn't find the answer. So, I try to find the equivalent of the "IN" clause for an linq to SQL request and i find something : IList<int> listClientId = GetAllClientIdByRegionLPS(statutRegion); if (!statutRegion.Equals("")) q = q.Where(p => listClientId.Contains((int)p.ClientId)); But that...

Stored-procedure or SQL-query to find recipes, which related ingredients list contains whole passed list of strings

sql-server,stored-procedures,linq-to-sql
I'm using .net, mssql, LinqToSQL. My mssql db has the following schema - Recipes are related to Ingredients. Now I want to let users to find recipes by several ingredients. I need a stored-procedure or query which I can use to select all those recipes. The number of searching ingredients...

C# string to number, TryParse returns true when Parse fails

c#,linq,linq-to-sql,number-formatting,tryparse
I have a column in my database (SQL Server) storing averages as varchar. Values are not always valid numbers, it can be something like : 0, 12, 5.5, .4, 4.0, 6., 5.21428571428571428571428571428571428571, X, fail, or simply empty text. When assigning variable with linq, I want to display : success if...

How to select first list item to null using Linq

c#,asp.net,linq,linq-to-sql
hi i have a drop down list and i'm trying to set the first list item to value null and the text to '---' so when selecting '---' from the drop down list the value will be null so have succeed doing this in the .aspx page using Sql data...

distinct values in a Linq to sql string.Join()

c#,.net,linq,linq-to-sql
I have a table which contains the schedule for a class. it contains the teacherid and name, datetime of the class and the course for that specific time. teacherid teachername startdate coursename 1 john 9/1/2014 10:00 math 2 john 9/2/2014 10:00 math 3 jane 9/3/2014 10:00 english 4 john 9/4/2014...

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...

How i can group this query?

sql,linq,linq-to-sql
I want group by order.orderID. is it possible? I try but I cant do it. Any ideas? var query = from order in mydb.Orders join customer in mydb.Customers on order.CustomerID equals customer.CustomerID join employee in mydb.Employees on order.EmployeeID equals employee.EmployeeID join shipper in mydb.Shippers on order.ShipVia equals shipper.ShipperID join orderdetail...

Linq To Sql Entity Framework Getting Nested Entities

entity-framework,c#-4.0,linq-to-sql
I'm trying to do an include of a nested entity. I assumed the below code would work but it does not recognize the CapitalMailOrders entitycollection. Can anyone point me in the right direction to include these? var result = db.Contacts .Include(a => a.IDXPageLinks) .Include(b => b.ReboGatewayLoginInfoes) .Include(c => c.SocialMedias) .Include(d...

Linq statement to return count and fields

c#,select,linq-to-sql,count
I am still in the later learning of Linq to SQL so bare with me. I need to be able to return the rows from a table where there are duplicates of a certain data column. The table contains numerous columns, but I only need to return the lastname, firstname,...

Parent and collection of child objects

c#,linq,linq-to-sql
Say I have a Customer class and an Invoice class. Customer contains a List<Invoice> property called Invoices. Can this be filled with a single Linq statement? Or do I need to select customers first and then do a foreach on them to pull invoices later? Something like: (from customer in...

DateTime conversion in Linq c#

c#,linq,linq-to-sql
I'm trying to write a query who select all data who was created today. Here is my request : public List<Projet> GetEnvoiMailBienvenueProjet(Site site) { var res = _context.Projet.Where( a => a.SiteId == site.SiteId && a.Client1.AdresseClient.EnvoiEmailBienvenue && a.Client1.AdresseClient.Email != null && a.Client1.AdresseClient.Email != "" && a.CreationDate.ToString("{0:MM/dd/yyyy}") == DateTime.Now.ToString("{0:MM/dd/yyyy}")); return res.ToList(); }...

Join 2 tables with multiple references in LINQ

c#,linq,linq-to-sql
I have 2 tables, Table1 and Table2. **Table1** Id Name ------------ 14443 Michael 55658 Brian 84321 Lisa 335896 NULL 1035 Maya 5221296 Brenda **Table2** Id1 Id2 MatchLevel -------------------------- 14443 5221296 0,5192 14443 84321 0,8647 14443 182347 0,6897 **1035 14443** 0,9999 14443 4572311 0,8569 63547 14443 0,9563 335896 14443 0,9418 14443...

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 =...

Can anyone explain me the DbContext.SubmitChanges() method while saving changes to database

c#,linq-to-sql,console-application,linq-to-objects
I am using Linq to Sql in my console application. I am into this scenario where I retrieve records from database and update each record and save by calling DbContext.SubmitChanges() DbContext being my context object instance I see two scenarios. When I update 3 records (meaning 3 rows/objects out of...

Getting null when concatenating string in linq

c#,.net,sql-server,linq,linq-to-sql
this is my query: var db = new data.MQSDataContextDataContext(); string MonthY = divWave.InnerText.Substring(0, divWave.InnerText.IndexOf(",")); int waveN = Convert.ToInt32(divWave.InnerText.Substring(divWave.InnerText.IndexOf(",") + 1, 2)); int WorsheetID = Convert.ToInt32(Request.QueryString["id"]); var wsItems = from wi in db.VendorOfferWorsheetItems where wi.WorksheetID == Convert.ToInt32(WorsheetID) select new resultsForGrid { id = wi.id, waveItemID = wi.waveItemID, MName = wi.MName, MQS_Code...

Batch update on object list using EntityFramework 6 and Linq

c#,linq,entity-framework,linq-to-sql
I want to create a function that accepts a list of objects with their properties already set and does a batch update or insert on them only making 1 call to the database. (UPDATE) Here is the working version: public static void BatchInsertProducts(IList<Product> productList) { using (var context = new...

Entity Framework query return my primary key plus $id

c#,json,asp.net-mvc,entity-framework,linq-to-sql
This is my model class public partial class TLog { [Key] public int idLog { get; set; } public string description { get; set; } public System.DateTime insertDate { get; set; } } And this is my query method: public static List<TLog> GetAllLogs() { var query = from log in...

How can I force varchar length in Linq To SQL

c#,sql-server,linq-to-sql
When in Linq To SQL I use user defined function like this [Function(Name = "udf_find_a", IsComposable = true)] public IQueryable<A> FindA([Parameter(DbType = "varchar(100)")] string keywords) { return CreateMethodCallQuery<A>(this, ((MethodInfo) (MethodBase.GetCurrentMethod())), keywords); } the resulting query always contains varchar(8000) as a parameter type. So I have to change the parameter type...

How to convert this sql statement to linq with this pattern

sql,sql-server,linq,sql-server-2008,linq-to-sql
How to convert this sql statement to linq. declare @S nvarchar(100) = 'www.abc.com/a/b/c/d/e/f/g/h/i' while PATINDEX('%[/]%' , @S) > 0 BEGIN SET @S = LEFT (@S,LEN(@S) - PATINDEX('%[/]%' , REVERSE(@S))) SELECT @S END ...

Linq to Sql check if multiple records exist

c#,sql-server,linq,linq-to-sql
When using Linq to Sql and updating something like a cross reference table where they could already be records in the table that would just need to stay there, records that will change and records that could be removed. What is the best practice as to handle this? I am...

Many-to-Many Query in Entity Framework 6 without Junction Tables

c#,linq,entity-framework,linq-to-sql
I have several entites involved in one-to-many and many-to-many relationships. Entity framework doesn't expose models for the generated junction tables, so I'm trying to figure out how to use navigation properties to produce the equivalent result to this query: select p.Name, p.Description, p.Version, p.Filename, f.Name as Platform, p.ReleaseNotesURL from packages...

DataContext.Executequery login failed

c#,sql-server,linq,iis,linq-to-sql
I use Linq to sql and have the following statement: myDataContext ctx = new myDataContext(); string query = "select * from myTable"; var mycollection = ctx.ExecuteQuery<t_entity>(query).ToList(); This code works well with my domain user but not with the user running the IIS application pool which has lower privileges. It is...

Iterate through collections and .Add

c#,linq,linq-to-sql,collections
I'm trying to store the foreach loop collection of data outside the loop for later use. Consider the following foreach loop. IList<DetailViewModel> storeAllLoopItems = new List<DetailViewModel>(); foreach (var item in _listOfItems) { var items = (from a in context.Employee join b in context.Orders on a.Id equals b.Id join d in...

Local Database for Windows Phone 8

c#,windows-phone-8,linq-to-sql,sql-server-ce,isolatedstorage
I have started working on windows phone development recently. I have come across how to maintain relational database locally from this link : LINQ TO SQL The program performs operations on the .sdf file. My question is does the LINQ TO SQL ORM use the SQL CE database by default?...

How do I tell linq2db how to translate a given expression, ie Split(char) into SQL when it does not know how to do so?

c#,linq,linq-to-sql,extension-methods,linq2db
I am using linq2db and while it works well enough for most CRUD operations I have encountered many expressions that it just cannot translate into SQL. It has gotten to the point where unless I know in advance exactly what kinds of expressions will be involved and have successfully invoked...

LINQ to SQL - WHERE clause filtered

linq,linq-to-sql
Why is the where condition not applied in the following code? When I search, the results are not getting filtered. When I debug, searchString does have a value and execution is going through the _data.Where clause but it's not returning the filtered result. What I'm doing wrong here? IQueryable<UserViewModel> _data...

Creating list of users when developing an organisational hierarchy in C#

c#,asp.net,asp.net-mvc,linq,linq-to-sql
So I have to build a hierarchy whereby the admin can create Teams and sub-teams. There can be infinite sub-teams and each Team has to have a manager that is already a member of it's parent team (but not the manager of it's parent team). My issue is this: How...

How to make a JOIN in LINQ to SQL equivalent to Not Equals

c#,sql,sql-server,linq,linq-to-sql
I've got a table: Year | Period | Account During a new period, I'd like to get a list of new accounts and a list of accounts that also existed in the prior period. Accounts that are also in the prior period: bs.DataSource = from c in accountsToImport join p...

Linq 2 SQL: composite primary key

c#,.net,sql-server,linq,linq-to-sql
I have a linking table with two foreign keys. Together, they are the primary key for the table. I am trying to map this in Linq: [Table(Name = "PartToPart")] public class PartToPart { [Column(Name = "PartID", IsPrimaryKey = true )] public int PartID { get; set; } [Column(Name = "ParentPartID",...

nested IN subquery in linq to sql

sql,linq,linq-to-sql
confused how to write this sql query into linq to sql select FirstName from [User] where [UserId] in( select LenderId from AffiliateLenderRelation where AffiliateId in (select Userid From [User] where [UserId] = 11) ) what i have tried so far var innerquery = from iq in db.AffiliateLenderRelations where iq.AffiliateId ==...

Using Linq to Sql join in generic handler

c#,linq,linq-to-sql,handler
I have some query that is working correctly but I just copied exact code without any modification to a new Generic Handler (ashx) file and I'm getting an error. This is the code: System.Collections.Generic.List<ChartPrice> ChartPrices = new System.Collections.Generic.List<ChartPrice>(); using (DatabaseDataContext db = new DatabaseDataContext()) { ChartPrices = (from p1 in...

Getting String List after join of two tables

c#,wpf,linq-to-sql
I have two tables Card(cardID, cardName, setName) and Set(setID, setName, setMark). I want add card names from a specific set to ComboBox items. String setMark = comboBoxSetMark.SelectedItem.ToString(); comboBoxCardName.ItemsSource = (from cards in dataContext.Cards join sets in dataContext.Sets on cards.setID equals sets.setID where sets.setMark == setMark select new { cards.cardName }).ToList();...

Query Linq to Sql Entity Framework c#

c#,linq,entity-framework,linq-to-sql
I have this query in SQL, but I do not know how can I do it in Linq This is the query SELECT GroupName, GroupsId FROM Groups WHERE GroupsId NOT IN (SELECT Groups.GroupsId FROM AssignGroups JOIN Groups ON AssignGroups.GroupsId = Groups.GroupsID AND AssignGroups.UsersId = 1) ORDER BY GroupName I'm using...

linq to sql select distinct and count of distinct

vb.net,linq-to-sql
I'm struggling creating a linq2 sql query. I have a table that contains records of media. One of the columns is media type e.g. CD, DVD etc. I need to pull a list that will give me each of the media types that exist (Select Distinct(mediatype)) and also a count...

Pre Operation Concurrent Plugins Getting MAXIMUM Existing Field Value For An Entity Records

linq,linq-to-sql,dynamics-crm-2011,dynamics-crm,dynamics-crm-2013
I am trying to set a field (Record Number) on an entity in Pre-Operation plugin that triggers on create with an incremental number based on previous one. For example if the MAX Previous Record Number is 4 I will set the one being created to 5. I am using LINQ...

Lazy loading occurring for some reason when DeferredLoadingEnabled is false

c#,.net,linq-to-sql,lazy-loading
I'm using LinqToSql and in order to prevent lazy loading each datacontext has this setting: DeferredLoadingEnabled = false Now, I'm using a data context that add's 3 sub tables as a loadoption. Something like: EntitiesDataContext dc = new EntitiesDataContext(connectionString); dc.DeferredLoadingEnabled = false; LoadOptions lo = new LoadOptions(); lo.LoadWith<Entity1>(e => e.Entity2);...

Why there is no change in my order Table while it shows the change in my DataGridView?

c#,winforms,visual-studio,linq-to-sql,datagridview
I want to use Linq to manipulate data in my local DB, northWind I have following the Creating and connecting processes of MSDN I have created NorthWind.dbml linq file and drop the two tables in it: After that I write this code in my form to retrieve data and to...

LINQ to SQL - Self Join Child to Parent (Same Table)

c#,sql,asp.net-mvc,linq-to-sql
For some reason I'm having a hard time wrapping my mind around how to do this. I have a table of message posts. (It is part of a very large existing legacy system, and I can't change the way the tables are defined.) There is only 1 table of posts....

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...

A problems with display data from linq queries in grid

c#,asp.net,.net,linq,linq-to-sql
I make this query. LinqSQl var query = from order in mydb.Orders join customer in mydb.Customers on order.CustomerID equals customer.CustomerID join employee in mydb.Employees on order.EmployeeID equals employee.EmployeeID join shipper in mydb.Shippers on order.ShipVia equals shipper.ShipperID join orderdetail in mydb.Order_Details on order.OrderID equals orderdetail.OrderID join product in mydb.Products on orderdetail.ProductID...

Linq-to-SQL combobox binding

c#,wpf,linq,linq-to-sql,combobox
I am trying to populate combobox items with list of objects that I retrieve from linq to sql in following way, (which only works for me with select new). Note that the Tax parameters are exactly the same as the select new. dt = new LilkaDataContext(); var taxes = from...

How can I capture the results of this Stored Procedure in a List<>?

c#,sql-server,tsql,stored-procedures,linq-to-sql
I have wrestled with this for at least 3 hours, and I am somewhat new to C#, especially with returning multiple rows from a stored procedure in SQL Server. I would like to return the results of a stored procedure : create procedure ingredientNames_givenDishId @dishid inT AS SELECT a.ingredientname --given...

LINQ Join to delete contents from multiple tables

c#,asp.net-mvc-4,linq-to-sql
I have a question about LINQ delete. I have 2 table in database and they are joined. First Table: empid, empname, salary, dname Second Table: dId, dname In the first Table I have many records with relations to the second table. I want to delete one employee in first table...

Linq To Sql, stored procedure returns multiple temp tables

c#,linq,c#-4.0,linq-to-sql
How can I return multiple temp tables with in a stored procedure using LINQ to SQL? I have a stored procedure, when called returns three temporary tables. How can I access the data in the temporary tables in .NET C# code using LINQ to SQL?

Convert Sql query to linq contains conditional OrderBy / ThenBy clause

c#,linq,linq-to-sql
tableA contains list of recent items that user has accessed, user has option to pin/remove the list. What I am trying is the list has to order by the below query. select * from tableA order by ispinned desc, case when ispinned = 1 then date end ASC, case when...

Is there any way to get all data retrieved between two points in C# code (Linq-to-sql)

c#,asp.net,unit-testing,linq-to-sql,mocking
My cenario is the follow: I'm working in one system developed in C# Asp.Net (a big, huge and definetly grown anyway system). And I'm trying to begin create some unit tests to start refactor (believe, it's need refactor (there some controllers with 10k, 12k lines). The problem is that a...

Linq to SQL query to find partial duplicates

c#,tsql,linq-to-sql
I have looked at lots of SO questions regarding Linq to SQL and duplicates but couldn't find anything that leads me in the right direction for my situation. I have a view that I want to query to return only rows that have the same ColumnA but a different ColumnB....

Index linq result using a dictionary or similar

linq,linq-to-sql,linqpad
I am using LINQ to SQL (through LINQPad) to extract some data from a MySQL database. var res = MyTable.First(); res.Dump(); And I have 255 columns like so. i0 | 0.01 i1 | 0.11 i2 | 4.01 ... i254 | 1.12 I would like to convert the values from the...

Error converting a LINQ query with a TimeSpan? to a List<>?

c#,list,linq-to-sql,nullable,timespan
I run the following LINQ to SQL query var q = from O in db.GetTable<OptionsTraded>() where O.TradeDate.Date == dtpVolReport.Value.Date select new { O.TradeTime }; but when I try and convert this output to a list: var qq = q.ToList(); I get the error: An unhandled exception of type 'System.InvalidCastException' occurred...

how to use update statement for updating more than two rows at a time in linq

linq,linq-to-sql
I have a table and I need to update 2 rows at a time. My SQL query will look like this: UPDATE tablename SET columnname=value WHERE tablename.id in (1,2) I need the same in LINQ. Thanks in advance....

How can I Check how many queries a LinqToSql DataContext ran?

c#,.net,linq-to-sql,datacontext
Is there any way I can check how many DB queries a DataContext ran? I know I can run an sql profiler but I want to analyze a big project with hundreds of queries. If not, is there any useful event that I can register to and count them? I'm...