How to get record form a different table based on a value from first table with linq expression?

» chash » How to get record form a different table based on a value from first table with linq expression?

By : user3100726

Date : January 12 2021, 09:11 PM

I wish this help you You can use let clause See Microsoft Docs

code :

public IQueryable GetAllMeeting()
        {
            var allMeeting = from xx in _dbContext.tbl_Meeting
                    let meetingCreatedBy = _dbContext.tbl_User.FirstOrDefault(x=>x.Id == xx.CreatedById)
                             select new Meeting
                             {
                                 Meeting_Attendee_Id = xx.Attendees,
                                 Meeting_Agenda = xx.Agenda,
                                 Meeting_Date = xx.Date,
                                 Id = xx.Id,
                                 Meeting_Subject = xx.Subject,
                                 CreatedById = xx.Created_By,

                                 CreatedBy = meetingCreatedBy !=null ? meetingCreatedBy.Name : "" //Or whatever property/column you have for displaying the name
                             };
              return allMeeting;
        }

Share :

how to use ADO recordset to create new table based on existing table and set the recordset(new table) as form's record s

By : Efthimis Tsopelas

Date : March 29 2020, 07:55 AM

it should still fix some issue i want to use ADO recordset to create new table based on existing table.then i want to set the new table as my form's record source.i know i can create a query and set the query as my form's record source but is it possible if i dont want to use this method? i want the form's record source to exist only when the form load. here's what i've done but still cannot set the form's record source to my recordset. , You seem to be working with two different ideas.

code :

Private Sub Form_Load()

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rst As New ADODB.Recordset
rst.ActiveConnection = cnn
Dim mySql As String
'create tblfrmQryOnHold based on tblOnHold
''Using aslias t for tblDisposition for clarity
mySql = "SELECT t.ID, t.DateRecorded, t.OrderNo, t.ArticleNo, "
mySql = mySql & "t.Description, t.Process, t.Defects, t.RefNo, "
mySql = mySql & "t.PostedBy, t.Status, t.Attachment, t.Engineer, "
mySql = mySql & "t.Remarks, t.ReviewClose, t.ScrapNo, t.HoldbackNo, "
mySql = mySql & "t.ProductionRemarks, t.HoldbackQuantity, t.HoldbackNum INTO "
mySql = mySql & "frmQryOnHold FROM tblDisposition As t;"

''Action query, so execute it aginst a connection
''This will fail if the table already exists, so it would be 
''much better to use DELETE FROM ... and INSERT INTO ...
''which would also cut down or bloat. However, the very best
''solution would be to just use a query on tblDisposition 
cnn.Execute mySql

''You now have created the table frmQryOnHold and can use it as @SoupyC 
''shows, or, if you wish to use recordset, as you seem to imply, then you want:

   With rst
     Set .ActiveConnection = cnn
     ''Something like this
     .Source = "select * from frmQryOnHold"
     .LockType = adLockOptimistic
     .CursorType = adOpenKeyset
     .CursorLocation = adUseClient
     .Open
  End With

'set form frmOnHold record source to form frmQryOnHold
Set Forms![frmOnHold].Recordset = rst


'set form frmOnHold record source to form frmQryOnHold
Forms![frmOnHold].RecordSource = frmQryOnHold
End Sub

Table and form based off a different table - see if a common record exists in the table and insert a "yes" or

By : user3224214

Date : March 29 2020, 07:55 AM

With these it helps I have an access database that my team uses to track projects. We use it as a VCB. , DCount() could make your code simpler.

code :

Dim lngComments As Long
Dim strComments As String

lngComments = DCount("*", "COMMENTS", "[PROJECTID] = " & Me.PROJECTID)
If lngComments > 0 Then
    strComments = "YES"
Else
    strComments = "NO"
End If
Me.CommentTxtBox.Value = strComments

=IIf(DCount("*", "COMMENTS", "[PROJECTID] = " & [PROJECTID]) > 0, "YES", "NO")

LINQ lambda expression subquery to find a parent record in the same table

By : FaceKickinIndustries

Date : March 29 2020, 07:55 AM

I hope this helps . Is it possible to do a lambda expression to run this query in C#? , Check this code:

code :

var l = Table.Where(x => Table.Where(n => n.Name == "child")
    .Select(n => n.ParentId).Any(y => y == x.Id)).Select(y=>y.Name);

class Person
{
    public int Id { get; set; }
    public int ParentId { get; set; }
    public string Name { get; set; }

    public Person(int id, int parentId, string name)
    {
        Id = id;
        ParentId = parentId;
        Name = name;
    }
}

Access, Getting info from another table to display on one form already using a different table record source based on 3

By : m.sadeghi

Date : March 29 2020, 07:55 AM

I hope this helps you . One option is to change your [Customer Reference] field in TaskCreation (the form) to a combo box. You can display whatever would be most useful to the user, but generally you'd want to keep the unique key (presumably [customer reference] or [customer id]) as the linked column, and any additional columns will provide descriptive data to allow the use to make the best selection. In the properties of the combo box be sure to appropriate update the Column Count and Column Widths -- '0' column width for the unique ID is probably the best practice assuming a numerical key.
Once the user selects this ID, you can populate additional non-linked textboxes by using something like DLOOKUP.

Linq select a record based on child table data

By : Zaero

Date : March 29 2020, 07:55 AM

Hope this helps This answer pointed me in the right direction, LINQ Query - Only get Order and MAX Date from Child Collection
Modified my query to the below.

code :

if (!string.IsNullOrEmpty(status)) {
{
    query = query
            .SelectMany(s => s.order_status
                              .OrderByDescending(o => o.DateStatusUpdated)
                              .Take(1)
                       )
            .Where(w => w.Status.Equals(status))
            .Select(s => s.order_tbl);
}