Simulating Cross Context Joins--Linq/C#

Simulating Cross Context Joins--LINQ/C#

Maybe something like this can get you started in the right direction. I made a mock database with similar columns based on your column names and got some results.

    class Program
{
    static AccountContextDataContext aContext = new AccountContextDataContext(@"Data Source=;Initial Catalog=;Integrated Security=True");
    static LoanContextDataContext lContext = new LoanContextDataContext(@"Data Source=;Initial Catalog=;Integrated Security=True");

    static void Main()
    {

        var query = from a in aContext.ACCOUNTs
                    join app in aContext.APPLICATIONs on a.GUID_ACCOUNT_ID equals app.GUID_ACCOUNT
                    where app.GUID_APPLICATION.ToString() == "24551D72-D4C2-428B-84BA-5837A25D8CF6"
                    select GetLoans(app.GUID_APPLICATION);

        IEnumerable<LOAN> loan = query.First();
        foreach (LOAN enumerable in loan)
        {
            Console.WriteLine(enumerable.GUID_LOAN);
        }

        Console.ReadLine();
    }

    private static IEnumerable<LOAN> GetLoans(Guid applicationGuid)
    {
        return (from l in lContext.LOANs where l.GUID_APPLICATION == applicationGuid select l).AsQueryable();
    }
}

Hope this helps!

Working with Cross Context Joins in LINQ-to-SQL

I've had to do this before, and there are two ways to do it.

The first is to move all the servers into a single context. You do this by pointing LINQ-to-SQL to a single server, then, in that server, create linked servers to all the other servers. Then you just create views for any tables you're interested from the other servers, and add those views to your context.

The second is to manually do the joins yourself, by pulling in data from one context, and using just the properties you need to join into another context. For example,

int[] patternIds = SessionDataProvider.Instance.ResultDataContext.Results.Select(o => o.patternId).ToArray();
var results = from p in PatternDataContext.Patterns
              where patternIds.Contains(p.PatternId)
              select p;

Though the first is easier to work with, it does have its share of problems. The problem is that you're relying on SQL Server to be performant with linked servers, something it is notoriously bad at. For example, consider this query:

var results = from p in DataContext.Patterns
              join r in DataContext.LinkedServerResults on p.PatternId equals r.PatternId
              where r.userId = 10;

When you enumerate this query, the following will occur (let's call the normal and linked servers MyServer and MyLinkedServer, respectively)

MyServer asks MyLinkedServer for the Results
MyLinkedServer sends the Results back to MyServer
MyServer takes those Results, joins them on the Patterns table, and returns only the ones with Results.userId = 10

So now the question is: When is the filtering done - on MyServer or MyLinkedServer? In my experience, for such a simple query, it will usually be done on MyLinkedServer. However, once the query gets more complicated, you'll suddenly find that MyServer is requesting the entire Results table from MyLinkedServer and doing the filtering after the join! This wastes bandwidth, and, if the Results tables is large enough, could turn a 50ms query into a 50 second query!

You could fix unperformant cross-server joins using stored procedures, but if you do a lot of complex cross-server joins, you may end up writing stored procedures for most of your queries, which is a lot of work and defeats part of the purpose of using L2SQL in the first place (not having to write a lot of SQL).

In comparison, the following code would always perform the filtering on the server containing the Results table:

int[] patternIds = (from r in SessionDataProvider.Instance.ResultDataContext.Results
                    where r.userId = 10
                    select r.PatternId).ToArray();
var results = from p in PatternDataContext.Patterns
              where patternIds.Contains(p.PatternId)
              select p;

Which is best for your situation is up to your best judgement.

Note that there is a third potential solution which I did not mention, as it is not really a programmer-solution: you could ask your server admins to set up a replication task to copy the necessary data from MyLinkedServer to MyServer once a day/week/month. This is only an option if:

Your program can work with slightly stale data from MyLinkedServer
You only need to read, never write, to MyLinkedServer
The tables you need from MyLinkedServers are not exorbitantly huge
You have the space/bandwidth available
Your database admins are not stingy/lazy

LINQ EF Join query from 2 different data context

LINQ and EF are pretty cool. But sometimes, its abstractions don't offer what you need.

Just fall back to base, write the query by hand, put it in a string, run it against yourcontext.YourDbSet with the SqlQuery method, and be done with it.

var query = @"SELECT * FROM dbData as entry
              INNER JOIN Users
              ON entry.UserId = Users.Id
              ORDER BY Users.Username";

yourcontext.dbData.SqlQuery(query);

If the abstractions offered to you don't work with what you need, abusing the abstractions to do something weird is far less clear than using the lower level interface.

LINQ to SQL two contexts

Linq2Sql does not support queries on multiple database context. However, your example looks pretty simple - find p.pID by upc and then use the value as an argument in second query.
It may look like this:

db = context1
db2 = context2

var upcId = (from p in db2.PROD_SKU 
            where p.UPC_NBR.ToString() == upc
            select p.pID).Single();

var query = from m in db.Merch
            join d in db.Dept on m.merchDept equals d.deptID.ToString()         
            join f in db.FOB on d.fobCode equals f.fobID
            join w in db.Item on m.merchID equals w.merchID
            join i in db.Doc on m.merchID equals i.MerchID
                        where m.pID == upcId;

LINQ code to combine two database tables?

Controller:

message = db.GetLogs();

int pageSize = 10;
int pageNumber = (page ?? 1);
var logs = message.OrderByDescending(i => i.timeStamp).ToPagedList(pageNumber, pageSize);

foreach (var log in logs)
    log.Name = Customer.Where(a => a.Value == log.customerId.ToString()).FirstOrDefault().Text;
                return PartialView("_LogPartialLayout", logs);

Model:

using System;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Collections.Generic;
using System.Web.Mvc;

namespace ASDMVC.Models
{
    [Table("Log")]
    public class LogModel
    {
        [Key]
        public long id { get; set; }
        public string message { get; set; }
        public DateTime timeStamp { get; set; }
        public string level { get; set; }
        public int customerId { get; set; }
        [NotMapped]
        public string Name { get; set; }
    }

    public class LogDBContext:DbContext
    {
        public LogDBContext() : base("MySqlConnection")
        {

        }

        public DbSet <LogModel> Log { get; set; }

        public IQueryable<LogModel> GetLogs()
        {
            return from log in Log
                   select log;
        }
    }
}

Can we do cross join in EF?

You can't do joins between different data context's. You would have to do the join with linq-objects

var crossJoin = from a in context.TableA.AsEnumerable()
                from b in context2.TableB.AsEnumerable()
                select new
                {
                  a,
                  b
                };

Linq Join Query - How to display new table in view MVC C#

I would suggest to create a new viewmodel Class to load data from 2 different tables like one below:

public class ProductSupplier
{
    public string ProName {get; set;}
    public string Price{get; set;}
    public string SupName{get; set;}
    public string SupPhone{get; set;}
}

Now when returning data from your controller you fill the model and return it to view as below:

public ActionResult JoinSupToPro()
{
    SupplierDBContext dbS = new SupplierDBContext();
    //Create list instance of your model
    List<ProductSupplier> model=new List<ProductSupplier>();
    var innerJoinQuery = (from pro in dbs.Products 
                         join sup in dbS.Suppliers 
                         on pro.SupplierId equals sup.ID
                         select new 
                         { 
                             proName=pro.Name,
                             proPrice=pro.Price,  
                             supName= sup.Name , 
                             supPhone=sup.Phone
                         }).ToList();//convert to List
    foreach(var item in innerJoinQuery) //retrieve each item and assign to model
    {
          model.Add(new ProductSupplier()
          {
                ProName = item.proName,
                Price = item.proPrice,
                SupName = item.supName,
                SupPhone = item.supPhone
          });
    }
    return View(model);
}

Once model is passed from controller you can display it in view as below:

//Refer the list instance of model in your view
@model IEnumerable<ProjectName.Models.ProductSupplier>
@foreach(var item in Model)
{
      //assign this values to any element of html
      //by referring it as @item.ProName, @item.Price etc.,
}

Hope it's clear

Simulating Cross Context Joins--Linq/C#