Add Custom Columns To A Table That LINQ To SQL Can Translate To SQL
Aug 26, 2009
I have a table that contains procedure codes among other data (let's call it "MyData"). I have another table that contains valid procedure codes, their descriptions, and the dates on which those codes are valid. Every time I want to report on MyData and include the procedure description, I have to do a lookup similar to this:
From m in dc.MyDatas _
Join p in dc.Procedures On m.proc_code Equals p.proc_code _
Where p.start_date <= m.event_date _
[Code]....
Is there a way to turn a complex lookup (i.e. a non-trivial join) like this into something SQL can recognize so that I can define it in one place and just reference the description as if it were a field in MyData? So far the only thing I can think of is to create a SQL view on MyData that does the linking and bring that into my data context, but I'd like to try to avoid that.
I have two tables Employees and CafeLogs. Some employees can be cashiers and also customers at the Cafe shop.
Table structures:
Employees: EmployeeId(PK) , FirstName, LastName CafeLogs: LogId (PK), CashierId, EmployeeId, Value, => CashierId and EmployeeId are the data from column EmployeeId of Empoyee table
Table relationship:
Employees 1:N CafeLogs (CashierId (FK))
[code]....
Right now I know how to select only LogId, Employee's name, and , Value, not with Cashier name yet.
Dim query = From log In db.CafeLogs _ Join emp In db.Employees On emp.EmployeeId Equals log.EmployeeId _ Select log.LogId, emp.FirsName, emp.LastName, log.Value
Some long-gone developer left the following LINQ query behind with no documentation and I'm struggling to understand what it does (and therefore if it's doing it right), either by breaking it into pieces or providing the SQL equivalent?
CODE:
I am struggling in particular with the Group Join and Into TargetMatches = Group pieces:
The following LINQ query resp. call of the extension method Select in Visual Basic 2010 is working fine:
Dim qAvSalary = qJobData.Select(Function(e) e.AvSalary) But doing so I am not able to specify the name of property I want the query (e.g. AvSalary) using a string variable. This should be possible if I use a LINQ expression tree. Searching and trying a long time on how to translate the query to a corresponding expression tree was not successful. My final approach is:
I have Dataset ds filled up with values Until now I was displaying values in GridView. Now I want that all the rows should be columns and columns should be rows.I have 2 options: Either 1 I can directly convert grid to columns and display it, or 2 I can convert the GridView to html and then write loops to convert. I was trying the 2nd option but I cant figure out how I should do that.[code]With this code I am still getting same as GridView. Please help me for converting rows to columns and vice versa.
I've the following code which successfully makes an average for all the columns from a table. What I need to do though is ignore certain columns in this equation.
Dim totalNumber as Double = 0 Dim count as Integer = 0 For x = 0 To xyz123.Tables(0).Columns.Count - 1
I want to translate MouseArgEvent.Location (x,y) coordinates into my custom graph coordinates. Mouse Y coordinates increase from top to bottom while my grid's y value increase from bottom to top. Given my following code, I would need to create 21,500 separate conditional expression for 100(y) x 215(x) possible coordinate locations for my grid.
Sub OnMouseMove(ByVal e As System.Windows.Forms.MouseEventArgs) If e.X > 48 AndAlso e.X < 50 AndAlso e.Y > 397 AndAlso e.Y < 399 Then MessageBox.Show("about 0,0")
My problem is with the tickets table, I have listed the user that created the ticket, the tech who will solve the ticket and a user that over sees the ticket. All three users reference the users table. So how do I can I query the tickets table and get all three users that reference the same table storing the users?
Table1: Tickets 1) Ticketnumber 2) EnteredBy User 100 3) Issue
[code]....
What I can do now is something like this:
Select Ticketnumber, EnteredBy, Issue, UserName FROM Tickets INNER JOIN Users ON Tickets.EnteredBy = Users.UserID
Rather than running numerous queries in Access I am trying to write a front end for a DB2 database. The Access database is getting to a size that it is causing Access 2007 to have reliability issues and frequent compacting is required. I have created the Dataset, the Table Adaptors, and the Connection String already, but when I put in a bit of code in to fill the Table Adaptors the remote connection I have to work is going crazy (chewed up about 180MB before I realised it was pulling data from work).
Before I started the project I copied the .accdb to my local PC HDD and I pointed the connection string to it, but it still looks like it is trying to pull data from the work network. If it was a small database I wouldnt be concerned but it is pulling major data every time I run it. I have only 2 out of the 4 Table Adaptors running and it didnt fill them even after 10 minutes.
Here is the code I was trying to run... Server name has been substituted for Server for obvious work reasons.
Because the tables are so large I was looking for a way to pull only the columns I need to from the tables, I tried adding .SOFT_ID to the first line of code but the TA spat the dummy message below...
Value of type 'System.Data.DataColumn' cannot be converted to Application_Name.DS_DB2Database.Server_SOFTDataTable'.
I would also like to pull just the data columns I need to speed up the query.
Note: In case youre wondering why I am not connecting directly to the DB2 database. I currently dont know where it is stored and while leveraging Access I am trying to put a level of separation between the app and the db for the time being, later I will try connect directly.
I am trying to make a simple search method using sql to linq in visual studio. In my database, i have the fields "Firstname" and "Lastname", and my search string is "name". How can i make a simple linq query which searches both fields?
in plain SQL i would do something like this: "SELECT (Firstname + Lastname) as 'Fullname' FROM table WHERE Fullname LIKE '%searchstring%'
I have a list of attachments that I need to group by clientCLID and EmailAddress. From this grouped list I only need a list of clientCLIDs. After fiddling for a while I've managed to get it to work as follows:
My requirement is as follows, I am calling a procedure and I'm populating a DataTable. Assume that the procedure is returning emp_id, emp_name, designation and status. When I use DataGridView1.DataSource = <Name of the DataTable>, I get all the columns populated into the DataGridView. How can I display only selected columns in the DataGridView. For e.g, I want to show only emp_id and emp_name.Similarly, as to how I can increase the width of the columns of the DataGridView by writing code.
Dim i As Integer For i = 1 To 20 Dim captionTextBox As TextBox = DirectCast(Me.FindControl("news_Image" +
[Code].....
Where the comment is in the code above, I need to insert the value of the textbox (text) into Linq columns. For example: articleToUpdate.news_Image1Caption, news_Image2Caption, but I need to change the number after news_ to the value of i in the for loop.
The following data is created by joining two sql tables together:I would like to group together distinct rows of DateStamp/UserName/ StudentName/ InstructorName/TableName/PrimaryKey (I'll call this 'group records') and then group under these ColumnName/PreviousValue/NewValue (I'll call this 'subgroup records')The end result would be that I could iterate through the 'group records' - there would be 5. In each 'group record', I could then iterate through the 'subgroup records'. The 5 groups would contain 3, 2, 5, 2 and 1 subgroup records respectively.What would be the syntax to create a query to do this? Ideally this would be in a vb.net linq syntax.
How to update columns in datatable using linq without conditions. I have an idea that I'm just gonna loop all the data in the datatable but don't know what comes in LINQ.
I attached a screenshot of my IRIS program and am wanting to implement a new feature, but I'm not sure how to do it. On the left hand side, you can see I have a bound datagridview control that displays a device name and an IP Address from a table in a database.What I want to do is have the option to launch a new form that has ALL the devices visible (including their state: green or red) and based on the size of the form, be able to auto-wrap to a new column.[code]So basically, instead of being able to only see the devices in the DGV on the left as it is scanning, I want to be able to see ALL of the devices in multiple columns in a new form. I looked on codeproject for a custom control to do this, but didn't really find anything.
I have a ListView (GridView) that I want to sort by 2 columns, so if 2+ items have the same value in Column 1, it sorts by Column 2. Pretty easy. But empty strings show up at the top when sorting A-Z. I'd like to move them to the bottom. I made a comparer (IComparer) that takes care of this, but I'm not sure how to use it.[code]...
Dim qry As <??> = From f In dirInfo.GetFiles("*.QBW") Select File = f.FullName, Include = True Dim dt As DataTable = qry.CopyToDataTable()
I tried as "IEnumerable(Of DataRow)" but that didn't work. At runtime it said:
Unable to cast object of type 'WhereSelectArrayIterator2[System.IO.FileInfo,VB$AnonymousType_02[System.String,System.Boolean]]' to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.
I'm still trying to get my head around the whole "Lambda Expressions" thing.
Can anyone here give me an example ordering by multiple columns using VB.Net and Linq-to-SQL using a lambda expression?
Here is my existing code, which returns an ordered list using a single-column to order the results:
Return _dbContext.WebCategories.OrderBy(Function(c As WebCategory) c.DisplayOrder).ToList
Note: The WebCategory object has a child WebPage object (based on a foreign key). I'd like to order by WebPage.DisplayOrder first, then by WebCategory.DisplayOrder.
I tried chaining the order bys, like below, and though it compiled and ran, it didn't seem to return the data in the order I wanted.
Return _dbContext.WebCategories.OrderBy(Function(c As WebCategory) c.DisplayOrder).OrderBy(Function(c As WebCategory) c.WebPage.DisplayOrder).ToList
How to JOIN tables on nullable columns? I have following LINQ-query, RMA.fiCharge can be NULL: Dim query = From charge In Services.dsERP.ERP_Charge _ Join rma In Services.dsRMA.RMA _ On charge.idCharge Equals rma.fiCharge _ Where rma.IMEI = imei Select charge.idCharge
I get a "Conversion from type 'DBNull' to type 'Integer' is not valid" in query.ToArray(): Dim filter = _ String.Format(Services.dsERP.ERP_Charge.idChargeColumn.ColumnName & " IN({0})", String.Join(",", query.ToArray)) So I could append a WHERE RMA.fiCharge IS NOT NULL in the query. But how to do that in LINQ or is there another option?
The problem was that the DataSet does not support Nullable-Types but generates an InvalidCastException if you query any NULL-Values on an integer-column. The modified LINQ-query from dahlbyk works with little modification. The DataSet generates a boolean-property for every column with AllowDbNull=True, in this case IsfiChargeNull.
Dim query = From charge In Services.dsERP.ERP_Charge _ Join rma In (From rma In Services.dsRMA.RMA _ Where Not rma.IsfiChargeNull Select rma) On charge.idCharge Equals rma.fiCharge _ Where rma.IMEI = imei Select charge.idCharge
