Share via

LINQ to SQL join 3 tables and select multiple columns

Question

Thursday, February 12, 2015 3:52 AM

hi i have three tables, Stock, InStock, OutStock

tables columns: 

- Stock: IdStock,CodeStock, Date

- InStock: Id_InStock, IdStock, mount_InStock

-OutStock: Id_OutStock, IdStock, mount_OutStock

Table relationship:

- Stock 1:N InStock (FK IdStock)

  • Stock 1:N OutStock (FK IdStock)

**Stock sample data:**

IdStock,CodeStock, Date

 - 8 , st10 , 03/12/2014
 - 11 , st20 , 02/12/2014
 - 12 , st25 , 09/12/2014

 - 14 , st27 , 03/12/2014

**InStock sample data**

Id_InStock, IdStock, mount_InStock

- 2, 8, 1250

- 3, 8, 100

- 5, 11, 250

**OutStock sample data**

Id_OutStock, IdStock, mount_OutStock

- 1, 8, 350

- 6, 12, 1100

- 7, 12, 750

i'd like to export all the data (InStock & OutStock) where date of stock between (02/12/2014 & 03/12/2014) in the same gridview

like my gridview column contains:

CodeStock   |  Date           | mount

st10             | 03/12/2014 | 1250

st10             | 03/12/2014 | 100

st20             | 02/12/2014 | 250

st10             | 03/12/2014 | 350

The first three line from InStock table and the last line from OutStock Table

so can you help me about the request should write to give this result and to bind the result in my gridview 

All replies (12)

Thursday, February 12, 2015 6:24 AM âś…Answered

Hi,

You need 2 queries to get 4 records.

1. First query Stock with the InStock and get the result

2. Query Stock with OutStock and get the result

3. UNION the result.

var res = (from s in stock
                       from cs in instock
                       where s.IdStock == cs.IdStock
                       where s.Date.Date >= DateTime.Parse("02/12/2014").Date && s.Date.Date <= DateTime.Parse("03/12/2014").Date
                       select new
                       {
                           CodeStock = s.CodeStock,
                           Date = s.Date,
                           Amount = cs.mount_InStock

                       }).ToList();

            var res1 = (from s in stock
                        from os in outstock
                        where s.IdStock == os.IdStock
                        where s.Date.Date >= DateTime.Parse("02/12/2014").Date && s.Date.Date <= DateTime.Parse("03/12/2014").Date
                        select new
                        {
                            CodeStock = s.CodeStock,
                            Date = s.Date,
                            Amount = os.mount_InStock

                        }).ToList();

            var output = res.Union(res1).ToList();

Thursday, February 12, 2015 5:03 AM

Hi,

try this

var query = (from s in context.Stock
             join cs in context.CodeStock on s.IdStock equals cs.IdStock
             join os in context.OutStock on s.IdStock equals os.IdStock
             where s.Date >= DateTime.Parse("02/12/2014) && s.Date <= DateTime.Parse("03/12/2014")
             select new 
             { 
                CodeStock = s.CodeStock, 
                Date = s.Date, 
                Amount = cs.mount_InStock 
             }).ToList();


GridView1.DataSource = query;
GridView.DataBind();

Thursday, February 12, 2015 5:28 AM

hi, 

join cs in context.CodeStock on s.IdStock equals cs.IdStock

is it true or for 

join cs in context.InStock on s.IdStock equals cs.IdStock

and in select new i must have the amount from InStock if i have the record and after that from OutStock if i have the record

so how can i do this 

Thursday, February 12, 2015 5:31 AM

var query = (from s in context.Stock
             join cs in context.Intock on s.IdStock equals cs.IdStock
             join os in context.OutStock on s.IdStock equals os.IdStock
             where s.Date >= DateTime.Parse("02/12/2014) && s.Date <= DateTime.Parse("03/12/2014")
             select new 
             { 
                CodeStock = s.CodeStock, 
                Date = s.Date, 
                Amount = (cs.mount_InStock == 0 || cs.mount_InStock == null) ? os.mount_InStock : cs.mount_InStock
             }).ToList();

Thursday, February 12, 2015 5:47 AM

the cs is it equal to context.InStock?

and in the same gridview i should present the (codeStock, date, mount_InStock) and (codeStock, date, mount_OutStock)

Thursday, February 12, 2015 5:52 AM

midoom

the cs is it equal to context.InStock?

s - Stock
cs - InStock
os - OutStock

Thursday, February 12, 2015 5:57 AM

ok thanks

Amount = (cs.mount_InStock == 0 || cs.mount_InStock == null) ? os.mount_InStock : cs.mount_InStock

and for this line how we take 

 (cs.mount_InStock == 0 || cs.mount_InStock == null)

i like juste to view the amount from InStock an OutStock like the example in my first post

thanks

Thursday, February 12, 2015 5:59 AM

Hi,

I just created the class and added your data and the query is as follows

You Get only 2 records matching.

 public class Stock
        {
            public int IdStock { get; set; }
            public string CodeStock { get; set; }
            public DateTime Date { get; set; }
        }

        public class InStock
        {
            public int Id_InStock { get; set; }
            public int IdStock { get; set; }
            public decimal mount_InStock { get; set; }
        }

        public class OutStock
        {
            public int Id_OutStock { get; set; }
            public int IdStock { get; set; }
            public decimal mount_InStock { get; set; }
        }


        public void GenerateData()
        {
            List<Stock> stock = new List<Stock>();
            stock.Add(new Stock() { IdStock = 8, CodeStock = "st10", Date = DateTime.Parse("03/12/2014") });
            stock.Add(new Stock() { IdStock = 11, CodeStock = "st20", Date = DateTime.Parse("02/12/2014") });
            stock.Add(new Stock() { IdStock = 12, CodeStock = "st25", Date = DateTime.Parse("09/12/2014") });
            stock.Add(new Stock() { IdStock = 14, CodeStock = "st27", Date = DateTime.Parse("03/12/2014") });

            List<InStock> instock = new List<InStock>();
            instock.Add(new InStock() { Id_InStock = 2, IdStock = 8, mount_InStock = 1250 });
            instock.Add(new InStock() { Id_InStock = 3, IdStock = 8, mount_InStock = 100 });
            instock.Add(new InStock() { Id_InStock = 5, IdStock = 11, mount_InStock = 250 });

            List<OutStock> outstock = new List<OutStock>();
            outstock.Add(new OutStock() { Id_OutStock = 1, IdStock = 8, mount_InStock = 350 });
            outstock.Add(new OutStock() { Id_OutStock = 6, IdStock = 12, mount_InStock = 1100 });
            outstock.Add(new OutStock() { Id_OutStock = 7, IdStock = 12, mount_InStock = 750 });

            var query = (from s in stock
                         join cs in instock on s.IdStock equals cs.IdStock
                         join os in outstock on s.IdStock equals os.IdStock
                         where s.Date.Date >= DateTime.Parse("02/12/2014").Date && s.Date.Date <= DateTime.Parse("03/12/2014").Date
                         select new
                         {
                             CodeStock = s.CodeStock,
                             Date = s.Date,
                             Amount = (cs.mount_InStock == 0) ? os.mount_InStock : cs.mount_InStock
                         }).ToList();




        }

Thursday, February 12, 2015 6:14 AM

i tested them and i have only 2 result but i should have 4 result :

3 results for IdStock=8  (2 from  InStock and 1 from OutStock)

and 1 result for IdStock=11 (from InStock table)

Thursday, February 12, 2015 6:34 AM

yes thanks :)

Friday, February 13, 2015 4:14 AM

i work with silverlight project in the web project i code my service layer with edmx file 

and in the principal project i code my different model, viewmodel and view

so when i used your solution i take the error 

Cannot implicitly convert type 'System.Collections.Generic.List<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable

i resolved this problem by add the new class :

i explain more in MyProject.Web.Services

in my class Service

public IQueryable<Event> SearchStockByDate(DateTime _dateDebut, DateTime _dateFin)
        {
            var result = (from s in this.ObjectContext.Stock
                       from cs in this.ObjectContext.Sortie
                       where s.IDStock == cs.IDStock
                       select new Event
                       {
                           CodeEvent = s.CodeStock,
                           DateEvent = s.DateSortieStock,
                           Montant = cs.Montant,
                       }).ToList().AsQueryable();
            if (_dateDebut != null)
            {
                result = result.Where(c => c.DateEvent >= _dateDebut.Date);
            }

            if (_dateFin != null)
            {
                result = result.Where(c => c.DateEvent < datefin);
            }
            return result;
        }

and i add a new class Event in MyProject.Web.Services

public class Event
    {
        private string _codeEvent;
        public string CodeEvent
        {
            get { return _codeEvent; }
            set { _codeEvent = value; }
        }
        private DateTime _dateEvent;
        public DateTime DateEvent
        {
            get { return _dateEvent; }
            set { _dateEvent = value; }
        }
        private double _montant;
        public double Montant
        {
            get { return _montant; }
            set { _montant = value; }
        }
    }

so when i call my method "SearchStockByDate in MyProject.model like this 

using MyProject.Web.Services
...
...
...
public void SearchStockByDate(DateTime _dateDebut, DateTime _dateFin, Action<IEnumerable<Event>, Exception> callback)
        {
            MyProjectContext _context = new MyProjectContext();

            EntityQuery<Event> qryStock = _context.SearchStockByDateQuery(_dateDebut, _dateFin);
            LoadOperation<Event> operationStock = _context.Load(qryStock);

            operationStock.Completed += (sender, eventArgs) =>
            {
                LoadOperation<Event> _sender = sender as LoadOperation<Event>;
                if (callback == null)
                    return;

                if (_sender == null)
                    return;

                if (_sender.HasError)
                    callback(null, _sender.Error);
                else
                    callback(_sender.Entities, null);
            };
        }

but i have this error 

The type or namespace name 'OrdreFab' could not be found (are you missing a using directive or an assembly reference?)

The type arguments for method 'System.Collections.ObjectModel.ObservableCollection<OrdreFab>.ObservableCollection(System.Collections.Generic.List<OrdreFab>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Sunday, February 15, 2015 3:11 AM

Hi midoom,

The type or namespace name 'OrdreFab' could not be found (are you missing a using directive or an assembly reference?) The type arguments for method 'System.Collections.ObjectModel.ObservableCollection<OrdreFab>.ObservableCollection(System.Collections.Generic.List<OrdreFab>)' cannot be inferred from the usage. Try specifying the type arguments explicitly

Form your description, I have a suggestion, please find out where is used 'OrdreFab', I can't find it in your provided code.

But if the project build succesful, please clean your project and remove the bin folder, then re-build agan. Maybe it's cache problem.

Hope this can be helpful to you.

Sherwin Zhao
Best Regards

  • Last updated on